Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

tricky formula to look up and transpose data

Hi Experts

How would you do the following: I need to lookup a employee id and then see what tasks they have completed. They may have completed multiple task against one id or just a single tasks. so for example.
col A (ID)      col B (Task)
X                  Clean
X                  Cash Up
X                  Stock Take  
X                  wash up

In this case employee X has completed the above task.  Waht I want to show in column C is the following using lookup or sum product or whatever is the better alternative is:

Col C (Total Task Complete)
Clean, Cash up, Stock Take, Wash up

There are a total of 5000 id' whats the best method?
Avatar of DarkoLord
Flag of Slovenia image


can you please explain in what way do you want the end result to look like (in the same table for all rows, in the same table for first occurence of the ID or in a separate table)?
Avatar of route217


In a separate table in no particular order..

And thanks for the feedback..
In the same table for the first occurrence of I'd sound better..
Would a user defined formula do?

Hi Sid

Would that work??? Not sure how one works??
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank matt

For the feedback let me test this out and come back...
Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

Small refinement to Metallimirk's approach, which eliminates the extraneous comma delimiters for tasks not performed:

=MID(IF(C2,", "&C$1,"")&IF(D2,", "&D$1,"")&IF(E2,", "&E$1,"")&IF(F2,", "&F$1,""),3,1000)

Or, to eliminate the need for helper columns altogether, you could use a formula like this:

=MID(IF(SUMPRODUCT((Data!$A$2:$A$14=A2)*(Data!$B$2:$B$14="Clean")),", Clean","")&IF(SUMPRODUCT((Data!$A$2:$A$14=A2)*(Data!$B$2:$B$14="Cash Up")),", Cash Up","")&IF(SUMPRODUCT((Data!$A$2:$A$14=A2)*(Data!$B$2:$B$14="Stock Take")),", Stock Take","")&IF(SUMPRODUCT((Data!$A$2:$A$14=A2)*(Data!$B$2:$B$14="Wash Up")),", Wash Up",""),3,1000)

Of course, these have a significant limitation: both use a fixed list of tasks, which must be known ahead of time.

By contrast, the PivotTable and UDF approaches allow for any number of tasks.

Which approach is most suitable for you, only you are in a position to judge :)

Thanks experts and a happy new year to u all..
And a happy new year to you too.

Any feedback on the suggestions thus far?  :)
Hi mat

Ur solution is the most practical... Just enjoying the evening, and looking forward to 12 midnight.

Once again thank you...