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's.......so whats the best method?
DarkoLord

Hi,

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

ASKER

In a separate table in no particular order..

And thanks for the feedback..

ASKER

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

Sid

ASKER

Hi Sid

Would that work??? Not sure how one works??
ASKER CERTIFIED SOLUTION
Patrick Matthews

membership
Create an account to see this answer
Signing up is free. No credit card required.

ASKER

Thank matt

For the feedback let me test this out and come back...
SOLUTION

membership
Create an account to see this answer
Signing up is free. No credit card required.
route217,

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

Patrick

ASKER

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

ASKER

Hi mat

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

Once again thank you...