List of names appearing multiple times but with different adjacent value

Hey good gentlemen and gentleladies of the forum,

Kindly look at the attached. I have explained my dilema there. But basically the scenario is this:

1. 1 table with two columns and 2000 rows
2. Names on Column A appear multiple times
3. Corresponding value on column B is for items they consumed. This is generally the same for all values in column A
4. However, every now and again they will consume something else.

And what I need is a list on a different sheet within the same workbook of all those people that appeared multiple times BUT only those that consumed more than 1 item. See attached to see the columns that I need on the filtered list. I know autofilter can do it, but then I'll have to go one by one to see those that consumed different product.

thanks,

Hi Ron,

I could get close with a pivot table but only with 2007 features. If you deleted dups before the pivot, I think I'd have it. See the attached with a helper column and 2010 pivot table.

Originally Posted by MarvinP

Hi Ron,

I could get close with a pivot table but only with 2007 features. If you deleted dups before the pivot, I think I'd have it. See the attached with a helper column and 2010 pivot table.

Yeah, I can't seem to get it closer than that with a pivot either. The thing is, that if you notice "Ted" is there consuming apples twice. I have hundreds of names like that where someone appears multiple times eating only one item several times... And this is what I don't want. I only what those who appear multiple times consuming more than 1 item.

I wanted to try arrays, but I really could not figure out how give me the items that appear several times only one time so that I could have simply use a SUMPRODUCT for the eating count on the third column.

Thanks for this though. Are you good with arrays?

Check out what I got from few more helper columns

Yeh - helper columns - and solved!!

Just in case the VBA code to automate the above becomes useful to anybody