add two list togeter and summing duplicates

[DRFILL]

I have two lists of names, some names are duplicates but not all. I need to create a list of unique names while adding adjacent cells.

List 1
Brees 1 0 0 35
Rodgers 0 1 0 32
Brady 1 0 0 29
Manning 0 1 0 25

List 2
Manning 1 0 0 45
Rodgers 0 1 0 33
Brees 0 0 1 50
Ryan 0 0 1 50

Needed
Brees 1 0 1 85
Brady 1 0 0 29
Manning 1 1 0 70
Ryan 0 0 1 50
Rodgers 0 2 0 65


Note: The order of the names will always be different

[dilipandey]

HI DRFILL,

May be u can use a pivot table, see attached:
summing duplicates.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[DRFILL]

to be honest I have never made a pivot table before. is there another way?

Let me add some more details. I will have six list on page 1. These list need combined with six list corresponding list on page 2. the new list will be on page two. Then on page 3 I will have six more list that will need to be combined with the list that was already combined on page 2 to form a weekly running total.

Im looking for a formula so after I get page two done I can just make a duplicate of the page, adjust a few cells and page three will be ready to go.

[Kevin UK]

Hi DRFILL

I have'nt used them a lot, but have a look here

[DRFILL]

Ok, I have played around with pivot tables now, WOW, they are awesome. Very useful tool. But not what I am looking for. I wish it was. It would save me alot of time. Thank you both for making me learn about them. I will be using them in the future now because of this, just not in this file.

There are multiple reasons why it wont work. the Data needs to be in the correct format so it can be copied over to a webpage each week. Also I am setting this file up as a template to use each year. so I enter stats into week one they get filtered to the correct places, Enter week 2 Boom its done...so on and so on for all 17 weeks. but with the table once I make it the values dont update when new data is entered.

I really need a formula.

[mike7952]

Give this a try

[DRFILL]

Works, I like it better then the pivot table but I would rather not use a macro. I'm starting to feel like a giant pain with this one but I am sure it can be done. what about if it was broke down into two parts, creating the unique list and then using a sumif..... just had a thought( the world really must be ending)
What if a created a page that holds all the list back to back so week 2 would follow week 1, 3 right after 2 so for each new week I just use a different longer list that includes the new week. I'm going to try that. if anyone can give me the formula for creating a new list while eliminating duplicates please do. Ill keep trying and see what I can come up with

[dredwolf]

The real problem here is the "new" name addition to the mix...I've been skulling it for a while and getting a headache, the rest is simple sum/sumif and math...if you had a list of say qb's that you needed the stats added for(or averaged for) it is all pretty straitforward..trying to add "new" names to the list is what' is killing me...hopefully, someone else has an idea....

still working it, just not sure it CAN be done short of VBA...

[DRFILL]

I figured it out let me finish building the worksheet and I will post it on here.

[dredwolf]

that would be great, since I've spent so much time on it, I would REALLY like to see the solution

Edit-
As an NFL fan and pool player, this REALLY does interest me

[FDibbins]

not sure what you came up with, but try this. assuming your data is in A2:E5 (list 1) and A8:E11 (list 2)...use this, copied down and across...

=IFERROR(INDEX($A$2:$E$5,MATCH($A22,$A$2:$A$5,0),COLUMN(B$1)),0)+IFERROR(INDEX($A$8:$E$11,MATCH($A22,$A$8:$A$11,0),COLUMN(B$1)),0)

I copy/pasted your examples from above = adjust references as needed

edit: if you had headings for the columns, you could use a 2nd match(), based on those headings, instead of the column() function

[dredwolf]

@FDibbins
also looking for week by week changing values, with possible new names in ANY week(ie-substitute quarterback/widereciever/runningback/etc....from what I see anyways )
and (I'm ASSUMING, wants the new name added into the list and totals calculated as well....this is where I got hung up...)

[DRFILL]

2012PlayerStats.sample.xlsx

Here is the file I am working on. it is the full file and note a sample so it is 700+kb.
I am setting this up to be a template that can be used each year to figure out stats for my FFL league where we do all the stats by hand. this is just one of the files I am creating to lighten the workload of the Commissioners who spend hours doing stats each week.

What I ended up doing is getting rid of the defined names(still need to delete them) and created a some formulas on page 2 in columns B-L that will will give me my ranges. Cell A1 is the week number. Change it and the ranges change with it. By doing this I eliminated my problem of having two list and get the added bonus of when I copy the sheet 16 times I only have to change that cell and it is ready to go.

To get the new list down to unique names I used the array formula in cell N3. After that it was simple sumif formulas.

Thank you all for your help on this issue. You might not have given me the answer I was looking for but you got the wheels spinning and taught me about some cool things that I had no clue about along the way.

[DRFILL]

@FDibbins

I have them in my file. I should have attached a workbook with my first post and I think this would have been solved a lot sooner, but I think I would still be using defined named ranges and I like where I am at with it now then I would be If I had to name everything or look up the ranges for all the different sum ranges.

[dredwolf]

DRFILL, I thank YOU for sharing your solution,I was driving myself insane trying to figure this out!
I could get the weekly results for any name in the list,or averages,or totals for weekly, but the new names not in the list were driving me batty...
So 1 Star at least for you, the satisfaction of having solved it yourself, and my heartfelt THANK YOU for the share

Edit-
I'll sleep a LOT better now