SOLVED

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

Copper Contributor

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

 

 

13 Replies

@robywoo 

Try this for Smith (1234).

 

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

 

best response confirmed by robywoo (Copper Contributor)
Solution

@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. 

bosinander_0-1646980401124.png

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.

@bosinander 

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

 

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

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

Hi @robywoo 

I assumed it was something like that and also think that @Detlef Lewin formula meets your needs.

The previously attached table in column L:M above does the same but for everyone at once.

Download the file, change to you actual data in columns A:C. It should already do what you want.

@bosinander I just tried the formulas in columns E:F (because it looks easier to understand than L:M on the existing data set but against the first entry eg 1234 the result is 0. It is like it is one row out. If I slide it up it matches the correct answer to the corresponding ID. I entered a fixed array rather than the whole column in the formula

Test Trial.png

 

 

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
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.
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?
Yes - since I did set it up using the full columns, headers where incuded.

How to change the below data in transpose form

 

Input Data :

 

Parent Biz UnitCodes
BLG01M782234
BLG01M781245
BLG01M788765
BLG01M788724
BLG01M787890
BLLG01SFL5678
BLLG01SFL2345
BLLG01SFL9877
BLLG01SFL5299
BLLG01SFL9477

 

Accepted output data :

 

Parent Biz UnitCode 1Code 2Code 3Code 4Code 5
BLG01M7822341245876587247890
BLLG01SFL56782345987752999477

@sunilkumar1988 Hi - see attached for one example.

 

bosinander_0-1669887785943.png

 

1 best response

Accepted Solutions
best response confirmed by robywoo (Copper Contributor)
Solution

@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. 

bosinander_0-1646980401124.png

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.

View solution in original post