Data --> Remove Duplicates
that helps you to create a unique list quickly without any formulation.
depends on what you try to do, you don't have to use the array formula discussed which serves another purpose.
Certainly a good idea, but manual. If a somewhat fast formula approach is wanted, for a generic (non-conditional) case we can have:
[TABLE="width: 202"]
<TBODY>[TR]
[TD="class: xl64, width: 91, bgcolor: transparent"]
ITEMS
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0
[/TD]
[TD="class: xl63, width: 115, bgcolor: transparent, align: right"]
3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Apple
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
1
[/TD]
[TD="class: xl64, bgcolor: transparent"]
Unique List
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Apple
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]
Apple
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Apple
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]
Banana
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Banana
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
2
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Grape
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Grape
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Grape
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
B1 must house a 0.
B2, copied down:
Rich (BB code):
=IF(ISNUMBER(MATCH(A2,$A$1:A1,0))+(A2=""),"",
LOOKUP(9.99999999999999E+307,$B$1:B1)+1)
C1, just enter:
Rich (BB code):
=LOOKUP(9.99999999999999E+307,B:B)
C3, just enter and copy down:
Rich (BB code):
=IF(ROWS($C$3:C3)<=$C$1,LOOKUP(ROWS($C$3:C3),B:B,A:A),"")
Note that the foregoing set up trades off memory against time (speed).