Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

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

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #7
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Cool. Thanks, guys.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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]),"")
    Microsoft MVP - Excel

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    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?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

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

    Thanks! Any other simplifications you could suggest?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    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.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
  •