date formula for extracting unique dates

elfudge35 · Jan 13th 2006

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

jonny · Jan 13th 2006

Re: date formula for extracting unique dates

elfudge35, use following code:

Code

Sub test()
    Range("a2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Index = 3
    For Each rng In Selection
        If rng.Offset(0, 0) <> rng.Offset(1, 0) And InStr(1, rng.Offset(0, 0), "Total") = 0 Then
            Cells(1, Index) = rng.Offset(0, 0)
            Index = Index + 1
        End If
    Next rng
End Sub

Display More

Good Luck!

elfudge35 · Jan 13th 2006

Re: date formula for extracting unique dates

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

jonny · Jan 13th 2006

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.

elfudge35 · Jan 13th 2006

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

elfudge35 · Jan 13th 2006

Re: date formula for extracting unique dates

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

Bob Phillips · Jan 13th 2006

Re: date formula for extracting unique dates

See the public NGs I answered ther.

elfudge35 · Jan 14th 2006

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

**Domenic** · Jan 14th 2006

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!

elfudge35 · Jan 14th 2006

Re: date formula for extracting unique dates

awesome, that did it, thanks

elfudge35 · Jan 16th 2006

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?

**Domenic** · Jan 16th 2006

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!

elfudge35 · Jan 16th 2006

Re: date formula for extracting unique dates

thank you domenic

elfudge35 · Jan 17th 2006

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?

**Domenic** · Jan 17th 2006

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?

elfudge35 · Jan 17th 2006

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

**Domenic** · Jan 17th 2006

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...

elfudge35 · Jan 17th 2006

Re: date formula for extracting unique dates

thank you

**Domenic** · Jan 17th 2006

Re: date formula for extracting unique dates

Here you go...

Hope this helps!

elfudge35 · Jan 17th 2006

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

date formula for extracting unique dates

Participate now!

Share

Users Viewing This Thread