Find Unique Items in an Array (Excel 97 SR-2)

Topic

New Reply

How would I find unique items in an array of values? Let’s say that I have the following:

Apples
Apples
Oranges
Pears
Oranges

I would like to return:

Apples
Oranges
Pears

Any thoughts?

Thanks

Reply | Quote

Replies

You can use “Data>Filter>Advanced Filter…” which includes options to filter unique records and copy the unique list to another place

Reply | Quote

Whoops! Found that I could return the array by using the Advanced Filter and choosing unique items. But could you write a custom function in VBA or an Array function to return those unique items?

Reply | Quote

Hi Steve

Take a look at Identifying Unique Items in an Array or Range by John Walkenbach and Duplicate And Unique Items In Lists by Chip Pearson.

Reply | Quote

Another Method from Walkenbach’s site is to use a collection.

Steve

Reply | Quote

Another Method from Walkenbach’s site is to use a collection.

Steve

Reply | Quote

Hi Steve

Take a look at Identifying Unique Items in an Array or Range by John Walkenbach and Duplicate And Unique Items In Lists by Chip Pearson.

Reply | Quote

Whoops! Found that I could return the array by using the Advanced Filter and choosing unique items. But could you write a custom function in VBA or an Array function to return those unique items?

Reply | Quote

Assume Cells A2:A6 house your data of : {“Apples”;”Apples”;”Oranges”;”Pears”;”Oranges”},

In Cell B2, enter and copied down to Cell B6 :

=IF(ROW()-ROW(B$2)+1<=SUM(1/COUNTIF($A$2:$A$6,$A$2:$A$6)),INDEX($A$2:$A$6,MATCH(1,–ISNA(MATCH($A$2:$A$6,B$1:B1,0)),0)),"")

Array formula, enter with Ctrl+Shift+Enter

Regards

Reply | Quote

Cool. Thanks, guys.

Reply | Quote

Cool. Thanks, guys.

Reply | Quote

I’m looking at this formula:

=IF(ROW()-ROW(B$2)+1<=SUM(1/COUNTIF($A$2:$A$6,$A$2:$A$6)),INDEX($A$2:$A$6,MATCH(1,–ISNA(MATCH($A$2:$A$6,B$1:B1,0)),0)),"")

and working through the pieces (wondering if it can be simplified). And I'm wondering, why the two minus symbols in front of ISNA?

Reply | Quote

ISNA returns a TRUE/FALSE value. — forces this into a numeric value (1 for TRUE, 0 for FALSE). You could also use 1*ISNA(…)

Reply | Quote

Thanks! Any other simplifications you could suggest?

Reply | Quote

I’ll leave that for Bosco_Yip for the moment…

Reply | Quote

I actually made it MORE complicated:
=IF(COUNTA($J$1:$J1)<=SUM(IF(COUNTIF($A$2:$A$15,$A$2:$A$15)=0,"",1/COUNTIF($A$2:$A$15,$A$2:$A$15))),
INDEX($A$2:$A$15,MATCH(1,–ISNA(MATCH($A$2:$A$15,J$1:J1,0)),0)),
"")
But it won't fail if there are blanks in the range now.

Reply | Quote

Assume the range located in A2: A15, together with some blanks

1] Enter WebGenii’s arrray formula in B2 :

{=IF(COUNTA($J$1:$J1)<=SUM(IF(COUNTIF($A$2:$A$15,$A$2:$A$15)=0,"",1/COUNTIF($A$2:$A$15,$A$2:$A$15))),INDEX($A$2:$A$15,MATCH(1,–ISNA(MATCH($A$2:$A$15,B$1:B1,0)),0)),"")}

2] Enter my simplified arrray formula in C2 :

{=INDEX(A:A,MATCH(0,1-ISNA(MATCH($A$2:$A$16,C$1:C1,0)),0)+1,0)&""}

Then, check should they give the same result

Regards
Bosco

Reply | Quote

Assume Cells A2:A6 house your data of : {“Apples”;”Apples”;”Oranges”;”Pears”;”Oranges”},

In Cell B2, enter and copied down to Cell B6 :

=IF(ROW()-ROW(B$2)+1<=SUM(1/COUNTIF($A$2:$A$6,$A$2:$A$6)),INDEX($A$2:$A$6,MATCH(1,–ISNA(MATCH($A$2:$A$6,B$1:B1,0)),0)),"")

Array formula, enter with Ctrl+Shift+Enter

Regards

Reply | Quote

Let A2:A6 house the sample you provided.

In B1 enter 0.

In B2 enter & copy down:

=IF((A2″”)*ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,””)

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:

which also calculates the number of distinct items.

In C2 enter & copy down:

=IF(ROW()-ROW(C$2)+1<=$C$1,INDEX(A:A,MATCH(ROW()-ROW(C$2)+1,B:),"")

Reply | Quote

Let A2:A6 house the sample you provided.

In B1 enter 0.

In B2 enter & copy down:

=IF((A2″”)*ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,””)

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:

which also calculates the number of distinct items.

In C2 enter & copy down:

=IF(ROW()-ROW(C$2)+1<=$C$1,INDEX(A:A,MATCH(ROW()-ROW(C$2)+1,B:),"")

Reply | Quote