Look up one name, return multiple (non-duplicate) results

**marklub** · 12-06-2012, 05:35 PM

Looking for some help with this issue. I have a large table of data, including the names of sales people, their corresponding accounts, and the revenue/profit for each account. The data is presented with multiple duplicates, rather than in one sum. For instance, "John Doe" is listed 32 times, with his accounts "Cogswell Cogs", "Spacely Sprockets", and "Joe's garage", listed 32 times total. On a separate sheet, I want to create a pull-down list of names (already complete), which when changed, will list each of the accounts that person has on multiple rows. So, when I choose "John Doe" from the list, it returns the 3 account names (no duplicates) on the rows below the drop-down of names. I also want to get the revenue and profit (total) for the accounts in the adjacent cells. I attached a sample file for clarification.

Thanks

**djapigo** · 12-06-2012, 05:43 PM

Hi markdub,

Have you tried Pivot Tables? This would be the perfect application...

**marklub** · 12-06-2012, 05:50 PM

Yeah, unfortunately, this is going to be a smaller part of a larger presentation of data that does not lend itself to a pivot table format. Good suggestion though!

**djapigo** · 12-06-2012, 06:46 PM

Hi Mark,

What makes this difficult (without the use of VBA/macros) is getting a list of the Account Names...

This is what I came up with... but I cheated and used helper columns (col. G in Data and col. A in Output)...

I'm sure other people can come up with a better way of getting a list of Account Names, but here's what I came up with...

Once you have that, then the summations are pretty straight forward...

Hope that helps...

Dennis

PS What exactly is wrong with the pivot table? Can you give an example...

PPS The reason I don't like using formulas in this example are all the "blank" formulas... personally, I don't like seeing them when they are not being used... that's why the pivot table works really well... again, I'm sure a macro can work well here too...