SOLVED

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

Occasional Contributor

# 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:

 1 1234 2 2345 2 5963 3 5964

Fig 1 (data set example)

 Position Employee ID 5851 Smith 1234 5851 Smith 1234 9162 jones 2345 9164 jones 2345 6332 black 2348 6332 black 2348 5003 barry 3422 5003 barry 3422 6904 joy 5623 6904 joy 5623 5851 fred 5963 6332 fred 5963 9164 bill 5964 6332 bill 5964 9164 bill 5964 5003 bill 5964 5003 bill 5964 9164 joe 2596 6332 joe 2596 5851 joe 2596

13 Replies

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

Try this for Smith (1234).

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

best response confirmed by robywoo (Occasional Contributor)
Solution

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

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.

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

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

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

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

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

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

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

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.

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

@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

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

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

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

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.

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

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?

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

Yes - since I did set it up using the full columns, headers where incuded.

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

How to change the below data in transpose form

Input Data :

 Parent Biz Unit Codes BLG01M78 2234 BLG01M78 1245 BLG01M78 8765 BLG01M78 8724 BLG01M78 7890 BLLG01SFL 5678 BLLG01SFL 2345 BLLG01SFL 9877 BLLG01SFL 5299 BLLG01SFL 9477

Accepted output data :

 Parent Biz Unit Code 1 Code 2 Code 3 Code 4 Code 5 BLG01M78 2234 1245 8765 8724 7890 BLLG01SFL 5678 2345 9877 5299 9477

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

@sunilkumar1988 Hi - see attached for one example.