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)))

 

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)

      • robywoo's avatar
        robywoo
        Copper Contributor
        Mar 11, 2022
        Hi I Thank you. I can explain in a little more detail - I have a list of employees who have an ID unique to them. Some employees are working in one position for let’s say 20 days and then again for 30 days. When this happens they will appear in my data twice. Then there may be an employee who works 10 days in one position (it could be the same position as someone else) - they may also work in 2 other positions for varying amounts of days. I need to be able to determine for each employee how many positions they are working in. I hope this helps

Resources