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
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » 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.
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.
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
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
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
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:),"")
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:),"")
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.