unique values

J

johnny

Hello,
I have a column that conntains years. The column is
sorted and probably will not have more than four unique
values. The range is about 700 cells. I would like to
define
Year1 as the earliest year
Year2 as the next..... and so on

If there is only one year, I would like Year2 = "-".

Any help getting me started would be great.
Thanks in advance.
 
T

Tom Ogilvy

Dim cell as Range, rng as Range
Dim Flag as Long
Dim Year1, Year2, Year3, Year4, Year5

set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
flag = 1
for each cell in range
if cell.offset(1,0).Value <> cell.value then
Select Case Flag
Case1
Year1 = cell.Value
Year2 = "-"
Case 2
Year2 = cell.Value
Year3 = "-"
Case 3
Year3 = Cell.Value
Year4 = "-"
Case 4
Year4 = Cell.Value
Year5 = "-"
End Select
Flag = Flag + 1
End if
Next
debug.print Year1, Year2, Year3, Year4, Year5

If would be easier to use an array.
 
J

JWolf

Year1=small(a1:a700,1)
Year2=if(small(a1:a700,2)=small(a1:a700,1),"-",small(a1:a700,2))
Year3=if(small(a1:a700,3)=small(a1:a700,2),"-",small(a1:a700,3))
....
....
YearN=if(small(a1:a700,N)=small(a1:a700,N-1),"-",small(a1:a700,N)).
 
Top