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

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

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

2. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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

3. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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?

4. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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.

5. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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

Steve

6. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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,MATC H(1,--ISNA(MATCH(\$A\$2:\$A\$6,B\$1:B1,0)),0)),"")

Array formula, enter with Ctrl+Shift+Enter

Regards

7. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

Cool. Thanks, guys.

8. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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.99 999999999999E+307,\$B\$1:B1)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:[img]/forums/images/smilies/cool.gif[/img]

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:[img]/forums/images/smilies/cool.gif[/img]),"")

9. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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,MATC H(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?

10. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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

11. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

Thanks! Any other simplifications you could suggest?

12. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

I'll leave that for Bosco_Yip for the moment...

13. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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.

14. ## Re: Find Unique Items in an Array (Excel 97 SR-2)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•