Excel - Make one list from two?

Shortstack

Hi Folks,

Hope this is the right place to get an answer for this.

I constantly have the need to combine two excel sheets into one. They have the same headings in each and only two columns. One Column is Name, and the other column is a number.

I would like to be able to automatically combine them and where a name appears in both sheets sum the number for this name and have only one entry for this name.

In simple form:

Sheet 1

Name Number
Bill 5
Ben 4
Ted 6

Sheet 2

Name Number
Bill 3
Ben 5
John 3

Combine to read:

Name Number
Bill 8
Ben 9
Ted 6
John 3

Is this possible and how? My lists are usually 200+ names long!

Cheers,

Mike

Basq

Using the VLOOKUP and SUM functions i'd imagine.

Use VLOOKUP on the name using the list of names to get a matching one and get the 1st column to the left. It'd be something like: VLOOKUP(NameCell,TableOfNamesAndNums,2,FALSE);

Use this on both lists and add the results returned from the VLOOKUP (number) using SUM. Sorry i'm not great at explaining Excel functions.

Tom Dunne

It's actually easier than that, if the cell the values are in doesn't change.

For example, if Bill and 5 are always in, say, Sheet 1, C4 and C5 and Bill and 3 are always going to be in, say, Sheet 2, C4 and C5, then on the third sheet, the total for Bill will be the following:

=Sheet1!C4+Sheet2!C4

It doesn't have to be C4 in both Sheet 1 and Sheet 2, the key thing is that the location doesn't change.

If the location of the values change, then you will have to do as Basquille says and use VLOOKUP.

seamus

Also check out the consolidation wizard. Can't remember exactly how it works, but I remember that this is exactly what it was used for (Tools > Consolidate, AFAIR)

Basq

Good point tom_dunne but judging from the data above, the positions of the names change row from sheet to sheet (i.e Ted and John in same positions on Sheet 1 and 2):

Sheet 1

Name Number
Bill 5
Ben 4
Ted 6

Sheet 2

Name Number
Bill 3
Ben 5
John 3

Might not be accurate data though. Am not familiar with the Consolidation Wizard that seamus mentioned anyways.

Seth Brundle

My advice is to go with a pivot table!


Go to the third sheet and select column A5
Go to tools > 'Pivottable & Pivotcharts'
Select 'Multiple Consolidation Ranges' and click next
Select 'Create single page...' & click next

Select Cols A & B from 1st sheet & click Add
Select Cols A & B from 2nd sheet & click Add
Click Next
Click Layout & you will see the pivot table structure.
In the graphic in the middle drag 'Page1' on the left away out of the graphic. Do the same with column
Double click the 'box' in the data area and change it from count to sum
Double click the 'box' in the row area and on the dialog click advanced - ob the left of the new dialog choose sort 'Ascending'.
OK dialog #2
OK dialog #1
OK the Layout window
Click finish

You may want to remove the blank row by unticking it in the drop down menu

Shortstack

Thanks for your help. Pivot table seems to do the job grand.