Merge and Sum Tables

[HamishM]

Hi,

I have two tables which get created several times per month.

I would like to be able to merge all entries in the two tables and if there are duplicate items in Col A then sum the value in Col B

e.g

Table 1:
Supplier Weight
Tim 5
Tom 7
Brad 3

Table 2:
Supplier Weight
John 4
Dave 3
Sarah 9
Tom 2


Merged Table:
Supplier Weight
Tim 5
Tom 9
Brad 3
John 4
Dave 3
Sarah 9

I have no problem using a macro or formula.

thanks in advance,
Hamish

 

[plettieri]

Hi:

Have you consiodered the pivot table approach?

plettieri

 

[plettieri]

Hi...

something like this..

Book1
	E	F	G	H	I	J
8	name	amt		name	amt
9	Tim	5		John	4
10	Tom	7		Dave	3
11	Brad	3		Sarah	9
12				Tom	2
13
14
15
16	Page1	(All)
17
18	Sum of Value	Column
19	Row	amt
20	Brad	3
21	Dave	3
22	John	4
23	Sarah	9
24	Tim	5
25	Tom	9
26	Grand Total	33
27
Sheet1

plettieri

 

[HamishM]

Thanks - that looks ideal.

I've not used pivot tables before, but have just attempted to recreate what you have - and failed. Can you take me through the steps you used please.

thanks
Hamish

 

[plettieri]

Hi:

Using the Pivot table wizard....DATA |PIVOT TABLE gets you started ...choosing "multiple consolidation ranges" button...and following the instructions from there..

post back if you more needed and or your results.

hope this helps

plettieri

 

[HamishM]

Ok...

i can get that far and recreate the table - however all the numbers are wrong. Every single cell in the table contains a 2 for some reason.

The original tables have one Col (A) of Names, and then two Cols (B0 (C) - where B is qty in lbs, and C is amount in $. Obvioiusly B, C don't need to sum.

help!

 

[HamishM]

All sorted now


Thanks for your help!