Figure out how much Halloween candy everyone got.

[Banaticus]

Susie:
tootsie-pop 3
oreo box 2
suckers 5

Bob:
tootsie-pop 2
suckers 1
nerds 4

I need to put together a list that shows (in total) how much candy everyone got. Sure, I could concatenate Susie & Bob's (and everone elses) lists, sort, duplicate, put a sum field into the duplicate list and delete repeat names, like...

nerds 4
oreo box 2
suckers 5
suckers 1
tootsie-pop 3
tootsie-pop 2

duplicate, then the second looks like:

nerds 4
oreo box 2
suckers =sum(!Sheet1,B3:B4) [or whatever the proper format looks like)
tootsie-pop =sum(!Sheet1,B5:B6)

But what if I have 15,000+ types of "candy"? It would be painful and tedious to put in all those sum fields. Any suggstions for how I would go about making a list that only shows item names once, but adds up the quantities for each given item in a particular worksheet?

[JBeaucaire]

Actually, you're not too far off from the simple solution.

1) put all the lists in one column, Column A...with number count in column B.
2) Use Data > Filter > Advanced Filter on the column, being sure to select the [ x ] Unique values only option as you copy to another location. Let's assume the Adv Filter set appeared in column D.
3) In the next column E, put in a simple SUMIF() formula now...in E1:
=SUMIF(A:A, D1, B:B)
4) Double-click the lower left corner of E1 to copy the formula down the whole dataset instantly.

Done.

[Banaticus]

Thanks, JBeaucaire, I really appreciate that -- you just saved me three days of work. Although, I'm a temp who's paid by the hour, so perhaps it's not such a good thing for me, but I just really don't like seeing people waste their time (especially me... even when I'm paid by the hour to "waste time").

[JBeaucaire]

Hehe, I understand completely.

I was once hired as a temp by a furniture company that was transferring all their historical files from one "layout" to another one sheet at a time. It was 1000s of files and they had worked out a "process" for doing it, and had calculated the time needed to accomplish was about 7 hours.

I was hired....used their process about 3 times, then made my own "process" and was done in 45 minutes. The customer was ecstatic, the temp agency....not so much.