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

in column c1:m1 and c2:m2 I put these formulas
c1=SMALL($A$2:$A$50,B2+1)
c2=COUNTIF($A$2:$A$50,C1)+B2

there is nothing in column b, but in column d-m it calculates the
occurances of all previous dates to know where to start the SMALL
function from


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile: http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237

date formula for extracting unique dates
 

oh, I forgot to mention, those two formulas do what I need, but I need
them combined into one formula and I can't figure that out


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile: http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237

date formula for extracting unique dates
 

can anybody help? I've got this code, but I need to create a function
with it if it can't be done with current functions

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


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile: http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237

date formula for extracting unique dates
 

On sheet2,

A1:=Sheet1!A1
B1: =IF(ISERROR(MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$1:$A $20&""),0)),"",
INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),
MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$1:$A$20&""),0)))

the latter is an array formula, so commit with Ctrl-Shift-Enter and copy
across.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"elfudge35" wrote
in message ...

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

in column c1:m1 and c2:m2 I put these formulas
c1=SMALL($A$2:$A$50,B2+1)
c2=COUNTIF($A$2:$A$50,C1)+B2

there is nothing in column b, but in column d-m it calculates the
occurances of all previous dates to know where to start the SMALL
function from


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile:
http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237

date formula for extracting unique dates
 

I guess nobody can do it?


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile: http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237

date formula for extracting unique dates
 

ok, but how do I keep the total lines out of that?


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile: http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237

date formula for extracting unique dates
 

I only need the dates, can you do something to get rid of the total
lines?


--
elfudge35
------------------------------------------------------------------------
elfudge35's Profile: http://www.excelforum.com/member.php...o&userid=30457
View this thread: http://www.excelforum.com/showthread...hreadid=501237