Table Merge - Need Assist

[flynavy]

I have two simple tables where I need to do a simple file merge. I could have easily done this in COBOL a billion years ago. But now... looking for a simple way in Excel or in Excel with a simple Macro.

Input Tables
	Key 1	Amount		Key 2	Amount
	1	$6.00		2	$5.00
	3	$3.00		3	$11.00
	4	$11.00		5	$3.00
	6	$25.00		6	$24.00
	9	$2.00		8	$1.00
				11	$16.00
Result
	1	$6.00
	2	$5.00
	3	$14.00
	4	$11.00
	5	$3.00
	6	$49.00
	8	$1.00
	9	$2.00
	11	$16.00

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[MickG]

Try this:-
For results as shown.
Nb:- Ensure Amounts are not Text. (i.e. Must be formatted numbers)

	A	B	C	D	E	F	G	H
1	Key 1	Amount		Key 2	Amount		1	£6.00
2	1	$6.00		2	$5.00		2	£5.00
3	3	$3.00		3	$11.00		3	14
4	4	$11.00		5	$3.00		4	£11.00
5	6	$25.00		6	$24.00		5	£3.00
6	9	$2.00		8	$1.00		6	49
7				11	$16.00		8	£1.00
8							9	£2.00
9							11	£16.00

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;"> <col width="34" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1194;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="20" style="width: 15pt; mso-width-source: userset; mso-width-alt: 711;"> <col width="64" style="width: 48pt;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

[COLOR="Navy"]Sub[/COLOR] MG20Jan03
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng1 = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Union(Rng1, Rng2)
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        Dic(Dn.Value) = Dic(Dn.Value) + Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Range("G1").Resize(Dic.Count, 2)
    .Value = Application.Transpose(Array(Dic.Keys, Dic.items))
    .Sort Range("G1"), xlAscending
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[flynavy]

Mick - great! Worked like a charm. I don't claim to understand the code yet, but I will. I have 18 months of VBA macro code under my belt. I love it. As you probably surmised, I'm an old guy who wrote COBOL in the late 1960's. And I tend to write VBA code in a COBOL fashion, which can work. But, I need to learn to use the VBA paradigm to take advantage of their tools. I'm retired after 42 years at a large energy company and IBM. Now I'm now working at my daughter's manufacturing facility doing tech and management consulting. I love every minute of it, especially being with one of my kids after 75% travel for decades. If I could indulge you one more time - I'm going to look hard at your code and try to modify it to include 1 more column in each table. I need a Quantity cell between the key and the amount, and I want to include Quantity in the merge. Like I said, I will figure it out using your code. What I'm asking you is this: is that a major or minor effort with what you've coded? Thanks again for your help. Even with what you've already given me, I'm sure I can run your existing code twice (once with amount and once with Quantity, and then cut and paste). It's guys and gals like you on this forum that have vastly accelerated my learning! Thanks again!

 

[MickG]

Thank you for your appreciation and enthusiasm.
Try this code on similar data as below:-

	A	B	C	D	E	F	G	H	I	J	K
1	Key	Quantity	Amount		Key	Quantity	Amount		Key	Quantity	Amount
2	1	4	$6.00		2	3	$5.00		1	4	£6.00
3	3	2	$3.00		3	6	$11.00		2	3	£5.00
4	4	6	$11.00		5	2	$3.00		3	8	£14.00
5	6	13	$25.00		6	13	$24.00		4	6	£11.00
6	9	2	$2.00		8	1	$1.00		5	2	£3.00
7					11	9	$16.00		6	26	£49.00
8									8	1	£1.00
9									9	2	£2.00
10									11	9	£16.00

<tbody>
</tbody>

[COLOR=navy]Sub[/COLOR] MG20Jan08
[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] Rng2 [COLOR=navy]As[/COLOR] Range, Rng [COLOR=navy]As[/COLOR] Range, Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Set[/COLOR] Rng1 = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Rng2 = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Rng = Union(Rng1, Rng2)
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        Dic.Add Dn.Value, Array(Dn.Value, Dn.Offset(, 1).Value, Dn.Offset(, 2).Value)
    [COLOR=navy]Else[/COLOR]
        Q = Dic(Dn.Value)
        Q(1) = Q(1) + Dn.Offset(, 1).Value
        Q(2) = Q(2) + Dn.Offset(, 2).Value
        Dic(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]With[/COLOR] Range("I2").Resize(Dic.Count, 3)
    .Value = Application.Transpose(Application.Transpose(Dic.items))
    .Sort Range("I1"), xlAscending
[COLOR=navy]End[/COLOR] With
Range("I1").Resize(, 3) = Array("Key", "Quantidy", "Amount")
Columns("K:K").NumberFormat = "$0.00"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick