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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Find Unique Items in an Array (Excel 97 SR-2)

    Author
    Topic
    #410387

    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

    Viewing 6 reply threads
    Author
    Replies
    • #881660

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

    • #881661

      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?

    • #881662

      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?

    • #881739

      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

      • #881903

        Cool. Thanks, guys.

      • #881904

        Cool. Thanks, guys.

      • #1121184

        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?

        • #1121190

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

          • #1121192

            Thanks! Any other simplifications you could suggest?

            • #1121194

              I’ll leave that for Bosco_Yip for the moment…

            • #1121238

              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.

            • #1123941

              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

    • #881740

      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

    • #882082

      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:),"")

    • #882083

      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:),"")

    Viewing 6 reply threads
    Reply To: Find Unique Items in an Array (Excel 97 SR-2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: