Mar 10 2022 08:03 PM
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 |
Mar 10 2022 08:22 PM - edited Mar 10 2022 09:00 PM
Mar 10 2022 10:41 PM
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.
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.
Mar 11 2022 12:54 AM
I got the impression from the desired output that @robywoo wanted only the numbers for a few selected IDs.
Mar 11 2022 01:20 AM
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)
Mar 11 2022 04:08 AM
Mar 11 2022 04:29 AM
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.
Mar 11 2022 04:37 AM
@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
Mar 11 2022 04:47 AM
Mar 11 2022 04:55 AM
Mar 11 2022 05:16 AM
Mar 11 2022 05:22 AM
Nov 30 2022 11:30 PM
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 |
Dec 01 2022 01:44 AM
Mar 10 2022 10:41 PM
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.
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.