Matching Unique List of Clients based on Multiple Columns

[scottyboy218]

I'm helping a colleague with a tricky workbook situation she has. She has a "database" in Excel where she has all the accounts our office handles, along with who works on them in various roles.

She's looking for a way be able to quickly summarize which employees are working on what accounts, along with revenue, etc. The tricky part is that each account is listed on a row once, and then there's 3 columns indicating who is working on it in a various role.

I've attached a sample of the layout of her file (B3:E7). I was able to successfully create a column that lists out every unique employee name, and has additional rows that factor in how many times a person shows up in the data. What I'm struggling with now is how to successfully populate column H, which would list out each of the accounts a person works on.

Any suggestions?

[FDibbins]

So you already have the list of names and how many of each there are?
eg 3 Bob's or 8 bob's or 1 bob?

[scottyboy218]

Correct, I've got column G already working based on a formula, I just need to populate column H.

[FDibbins]

and a name can only appear once in a column, correct?

[scottyboy218]

Nope - but I see now that my example poorly illustrated that. Bob might be listed as the primary contact for multiple accounts, so he'd show up in column C a few times.

[FDibbins]

try this...
=IFERROR(INDEX($B$4:$B$7,MATCH(G4,INDEX($C$4:$E$7,,COUNTIF($G$4:G4,G4)),0)),"No Contact")
copied down

[FDibbins]

hmm should have refreshed 1st, just saw you reply in the qty.
My suggestion wont pick up extra names

[scottyboy218]

I think I got it working, although not the cleanest. I added 3 columns to the left of the client name columns, and then combined the contact name with the count of how many times the person's name appears in the dataset (locked on row 4, so it drags as it goes down). So in the new columns, A4 would say "Bob1", B4 would say "Sam1". A5 would say "Sam2".

Then I just kinda brute forced it with iferror vlookups to check each column

[FDibbins]

Nice. Can you show me a copy of your file, please?

[scottyboy218]

Sure thing, here you go!

[FDibbins]

Nicely done.

Here is another, slightly longer, version...
=IFERROR(INDEX($E$4:$E$8,MATCH(K4&J4,INDEX($A$4:$C$8,,IF(COUNTIF($A$4:$A$8,K4&J4)=1,1,IF(COUNTIF($B$4:$B$8,K4&J4)=1,2,IF(COUNTIF($C$4:$C$8,K4&J4)=1,3,0)))),0)),"Not found")