Unique values of a range

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Anyone has an idea how to select the unique values of a
non-sorted(!) range (kinda select distinct) into a vector
(another range) by using worksheet functions ONLY (no VBA,
no own addin, no adv. filter, no interaction, etc.)?

Joe
 
You can add a column to the left of the column of duplicate data. With
your data starting in cell B2, enter the following formula in cell A2:
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")

Copy this down to the last row of data.

In a column to the right, enter the following formula in row 2:
=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2))

NameCount is a dynamic range for the items in column A.
NameList is a dynamic range for the items in column A:B.

Copy the formula down as far required.

There's a sample workbook here:

http://www.contextures.com/excelfiles.html#Function

under the subheading: Extract List of Unique Items
 
One way

assuming your range is in A3:A200

=INDEX($A$3:$A$200,MATCH(0,COUNTIF($D$2:D2,$A$3:$A$200),0))

entered with ctrl + shift & enter

where D2 is the cell above the one you put the formula in, copy down until
you get
an error
 
Back
Top