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 entry in col A, bill has 5 entries but 3 are unique, Fred has 2 entries in Col C but has 2 unique entries in Col A. So my resulting spreadsheet would be 2 columns with entries like:

 

11234
22345
25963
35964

 

 

Fig 1 (data set example)

PositionEmployee ID
5851Smith1234
5851Smith1234
9162jones2345
9164jones2345
6332black2348
6332black2348
5003barry3422
5003barry3422
6904joy5623
6904joy5623
5851fred5963
6332fred5963
9164bill5964
6332bill5964
9164bill5964
5003bill5964
5003bill5964
9164joe2596
6332joe2596
5851joe2596

 

 

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.

    • 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
      Steel 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.

Resources