date formula for extracting unique dates

E

elfudge35

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
 
E

elfudge35

can anybody help? I've got this code, but I need to create a functio
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 Su
 
B

Bob Phillips

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)
 
Top