# date formula for extracting unique dates

• I need to take a column that is in this format:
1/1/06
1/1/06 total
1/4/06
1/4/06
1/4/06 total
2/7/06
2/7/06 total
and so on

and make it into a column heading automatically, so that any time the dates are changed or the totals move or anything, the dates at the top row automatically change with it

1/1/06 1/4/06 2/7/06, etc

I've been able to do it in two rows, but I can't figure out how to combine them into one formula
I've attached that much and any help completing the task would be much appreciated

## Files

• Re: date formula for extracting unique dates

elfudge35, use following code:

Good Luck!

• Re: date formula for extracting unique dates

I hate to be a bother jonny, but could you put that into a function?

• Re: date formula for extracting unique dates

Any function returns value for one cell only and you want to get all unique values. In this case I know solution by code only.

• Re: date formula for extracting unique dates

I want the function to go in all cells at the top and based on what is in the previous cell to the left, I want it to look for the next date on the list, look at my file

thanks

• Re: date formula for extracting unique dates

anybody else have any suggestions real quick, I gotta go home

• Re: date formula for extracting unique dates

See the public NGs I answered ther.

HTH

Bob

• Re: date formula for extracting unique dates

I saw that, but I replied, that formula helped somewhat, but it didn't eliminate the total lines, I just want the dates

• Re: date formula for extracting unique dates

In your example, not all dates have a 'Total' cell. If you ensure that each date has a 'Total' cell, you can use the following formula...

C1, copied across:

=INDEX(\$A\$2:\$A\$46,SMALL(IF(ISTEXT(\$A\$3:\$A\$47),ROW(\$A\$2:\$A\$46)-ROW(\$A\$2)+1),COLUMNS(\$C1:C1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. See the attached file...

Hope this helps!

## Files

• Re: date formula for extracting unique dates

awesome, that did it, thanks

• Re: date formula for extracting unique dates

ok, now I have a new problem, what if they are not in order, what if there are multiple pools that are separated down the first column, but I need all of the dates in order across the top row?

• Re: date formula for extracting unique dates

Try...

=SMALL(IF(ISNUMBER(\$A\$2:\$A\$46),IF(MATCH(\$A\$2:\$A\$46,\$A\$2:\$A\$46,0)=ROW(\$A\$2:\$A\$46)-ROW(\$A\$2)+1,\$A\$2:\$A\$46)),COLUMNS(\$C\$1:C1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

• Re: date formula for extracting unique dates

thank you domenic

• Re: date formula for extracting unique dates

ok, another problem, the function works perfectly except for the fact that it recalculates after I do anything in the workbook because it is a volatile array function and it slows me down a great deal

is it possible to do it with a non array function?

• Re: date formula for extracting unique dates

Quote from elfudge35

ok, another problem, the function works perfectly except for the fact that it recalculates after I do anything in the workbook because it is a volatile array function and it slows me down a great deal

When you said previously that there could be "multiple pools that are separated down the first column", did you mean that there could be more than one set of the same date. For example, you could have more than one cell having '4/1/2005 Total', is this right?

Quote

is it possible to do it with a non array function?

Yes. It would involve using several formulas in helper columns. Would that be okay?

• Re: date formula for extracting unique dates

yes, there are more than one with a certain date

and I was hoping it could all be done with just one formula, but I'm starting to see I will need more, that's fine

• Re: date formula for extracting unique dates

Quote from elfudge35

yes, there are more than one with a certain date

Okay, that's what I understood. I just wanted to make sure...

Quote

I was hoping it could all be done with just one formula, but I'm starting to see I will need more, that's fine

Great! Give a few minutes and I'll post a solution along with a sample file...

• Re: date formula for extracting unique dates

thank you

• Re: date formula for extracting unique dates

Here you go...

Hope this helps!

## Files

• Re: date formula for extracting unique dates

ok, problem with that, there are some dates that repeat again, but not all the dates that are in the other pools are in the first pool

like this:
pool 1
2/21/03
2/21/03 total
4/1/03
4/1/03 total
7/1/03
7/1/03 total
pool 2
2/21/03
2/21/03 total
5/16/03
5/16/03 total

then the 5/16 would come after the 7/1/03

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!