Forum Discussion

robywoo's avatar
robywoo
Copper Contributor
Mar 11, 2022
Solved

Return unique values in one column but with duplicate entries in another column for Excel

I have a spreadsheet where I need to know of the employees' ID in column C how many unique entries there are in column A. For example (Fig 1) Smith (1234) has 2 entries in col C but has 1 unique entr...
excel
Formulas and Functions
  • bosinander's avatar
    bosinander
    Mar 11, 2022

    Detlef_Lewin Hi.

    With Excel 365 robywoo's formula can be extended to spill the resulting table from one cell, L2.

    The attached file has it built in three steps. 

    The LET functions makes it possible to use helper columns in the same formula.

    Changing the last parameter from the final output to eg IDs is a way to debug or analyse such formula.

    =LET(IDs;UNIQUE(C:C);
counter;BYROW(IDs;LAMBDA(id;COUNT(UNIQUE(FILTER(A:A;C:C=id)))));
includingLabels;CHOOSE({1\2};counter;IDs);
output;INDEX(includingLabels;SEQUENCE(ROWS(includingLabels)-2;;2);SEQUENCE(;COLUMNS(includingLabels)));
output
)

     You could also add more functionality like changing row 5 to

    SORT(output) or SORT(output,2) to sort by first or second column.

    NB - my Excel uses national settings ; as delimiter between parameters instead of US ,

    Easiest to use the attached file.

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Mar 11, 2022

robywoo 

Try this for Smith (1234).

 

=COUNT(UNIQUE(FILTER(A2:A21,C2:C21=1234)))

 

  • robywoo's avatar
    robywoo
    Copper Contributor
    Mar 11, 2022
    Thank you. I do like this as it looks simple. Plus using and applying the Unique formula to column C in a separate table with be good for my bigger data set
    • robywoo's avatar
      robywoo
      Copper Contributor
      Mar 11, 2022
      I just did it with my bigger data set and the values seemed to line up. Do you know what perhaps it didn't line up in the screenshot that I sent? Did it count the header as a cell?
      • bosinander's avatar
        bosinander
        Iron Contributor
        Mar 11, 2022
        Yes - since I did set it up using the full columns, headers where incuded.
    • bosinander's avatar
      bosinander
      Iron Contributor
      Mar 11, 2022
      Glad to hear 🙂 I think it is a good idea to choose a complexity level that suits your needs, and you still have the extra possibilities if you should find them plausible in the future.
  • bosinander's avatar
    bosinander
    Iron Contributor
    Mar 11, 2022

    Detlef_Lewin Hi.

    With Excel 365 robywoo's formula can be extended to spill the resulting table from one cell, L2.

    The attached file has it built in three steps. 

    The LET functions makes it possible to use helper columns in the same formula.

    Changing the last parameter from the final output to eg IDs is a way to debug or analyse such formula.

    =LET(IDs;UNIQUE(C:C);
counter;BYROW(IDs;LAMBDA(id;COUNT(UNIQUE(FILTER(A:A;C:C=id)))));
includingLabels;CHOOSE({1\2};counter;IDs);
output;INDEX(includingLabels;SEQUENCE(ROWS(includingLabels)-2;;2);SEQUENCE(;COLUMNS(includingLabels)));
output
)

     You could also add more functionality like changing row 5 to

    SORT(output) or SORT(output,2) to sort by first or second column.

    NB - my Excel uses national settings ; as delimiter between parameters instead of US ,

    Easiest to use the attached file.

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Mar 11, 2022

      bosinander 

      I got the impression from the desired output that robywoo wanted only the numbers for a few selected IDs.

       

      • bosinander's avatar
        bosinander
        Iron Contributor
        Mar 11, 2022

        Detlef_Lewin Hi

        1. Sorry for wrong reference. It's of corse your base formula

        2. I may have misinterpreted the origin wish/need, but for my own experience and classes it may be a good example to have the solution fully dynamical.

        Having them all in one dynamic table it is also possible to append a FILTER to or instead of the mentioned SORT :o)

Resources