# Function - Merge / Matching Question

#### ExcelMercy

##### Board Regular
Hello all,

I'm trying to merge two columns with two different columns (different sizes) and then have another column tell whether the data appeared in both or just one.

Example Data:

 Num_1 Name_1 Num_2 Name_2 A111 Jim A111 Jim A123 John A111 Cory B134 Susan A124 Marcus B134 Lori B121 Donald C786 Billy B134 Mark D555 John B134 Lori D555 Mark C786 Billy F121 Kim C786 Donny F124 Bonnie D124 Thomas F475 Brandon D124 Austin G857 Bill D124 Stacy D555 John D558 Justin F121 Kim F124 Bonnie F475 Brandon G857 Bill

<tbody>
</tbody>

Example Output:

 Match_Num Match_Name MATCH RESULTS A111 Jim In Both A111 Cory In "2" Only A124 Marcus In "2" Only B121 Donald In "2" Only B134 Mark In "2" Only B134 Lori In Both C786 Billy In Both C786 Donny In "2" Only D124 Thomas In "2" Only D124 Austin In "2" Only D124 Stacy In "2" Only D555 John In Both D558 Justin In "2" Only F121 Kim In Both F124 Bonnie In Both F475 Brandon In Both G857 Bill In Both A123 John In "1" Only B134 Susan In "1" Only D555 Mark In "1" Only

<tbody>
</tbody>

Any help would be greatly appreciated!

Last edited:

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### MickG

##### MrExcel MVP
Try this for results starting "F1".
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG07Dec26
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Ac = 1 To 3 [COLOR="Navy"]Step[/COLOR] 2
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, Ac), Cells(Rows.Count, Ac).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = Dn.Value & Dn.Offset(, 1).Value
[COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
Col = IIf(Ac = 1, 1, 2)
.Add Txt, Array(Dn.Value, Dn.Offset(, 1).Value, "In Col " & Col & " Only")
[COLOR="Navy"]Else[/COLOR]
Q = .Item(Txt)
Q(2) = "In Both"
.Item(Txt) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR] Ac
Range("F1").Resize(, 3).Value = Array("Match No", "Match Name", "Match Result")
Range("f2").Resize(.Count, 3) = Application.Transpose(Application.Transpose(.items))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

#### ExcelMercy

##### Board Regular
God amongst us mere mortals! Thanks Mick!

Last edited:

#### ExcelMercy

##### Board Regular
Try this for results starting "F1".
Code:
``````[COLOR=Navy]Sub[/COLOR] MG07Dec26
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Txt [COLOR=Navy]As[/COLOR] [COLOR=Navy]String,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] Col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] Q [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] Ac = 1 To 3 [COLOR=Navy]Step[/COLOR] 2
[COLOR=Navy]Set[/COLOR] Rng = Range(Cells(2, Ac), Cells(Rows.Count, Ac).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
Txt = Dn.Value & Dn.Offset(, 1).Value
[COLOR=Navy]If[/COLOR] Not .Exists(Txt) [COLOR=Navy]Then[/COLOR]
Col = IIf(Ac = 1, 1, 2)
.Add Txt, Array(Dn.Value, Dn.Offset(, 1).Value, "In Col " & Col & " Only")
[COLOR=Navy]Else[/COLOR]
Q = .Item(Txt)
Q(2) = "In Both"
.Item(Txt) = Q
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]Next[/COLOR] Ac
Range("F1").Resize(, 3).Value = Array("Match No", "Match Name", "Match Result")
Range("f2").Resize(.Count, 3) = Application.Transpose(Application.Transpose(.items))
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]``````
Regards Mick

Hey Mick - Thanks again for the help!

How would I update this if I wanted to check 4 columns against 4 columns instead of 2 v 2?

Example Data:
 Num_1 Name_1 City_1 State_1 Num_2 Name_2 City_2 State_2 A111 Jim Nashville TN A111 Jim Nashville TN A123 John Austin TX A111 Cory Seattle WA B134 Susan Miami FL A124 Marcus Detroit MI B134 Lori New York City NY B121 Donald Chicago IL C786 Billy Chicago IL B134 Mark San Antonio TX D555 John Houston TX B134 Lori New York City NY D555 Mark Phoenix AZ C786 Billy Chicago IL F121 Kim Jacksonville FL C786 Donny Houston TX F124 Bonnie San Antonio TX D124 Thomas Houston TX F475 Brandon San Diege CA D124 Austin Los Angeles CA G857 Bill Los Angeles CA D124 Stacy Chicago IL D555 John Houston TX D558 Justin San Diege CA F121 Kim Jacksonville FL F124 Bonnie San Antonio TX F475 Brandon San Diege CA G857 Bill Los Angeles CA

<tbody>
</tbody>

Example Results:
 Match_Num Match_Name Match_City Match_State MATCH RESULTS A111 Jim Nashville TN In Both A111 Cory Seattle WA In "2" Only A124 Marcus Detroit MI In "2" Only B121 Donald Chicago IL In "2" Only B134 Mark San Antonio TX In "2" Only B134 Lori New York City NY In Both C786 Billy Chicago IL In Both C786 Donny Houston TX In "2" Only D124 Thomas Houston TX In "2" Only D124 Austin Los Angeles CA In "2" Only D124 Stacy Chicago IL In "2" Only D555 John Houston TX In Both D558 Justin San Diege CA In "2" Only F121 Kim Jacksonville FL In Both F124 Bonnie San Antonio TX In Both F475 Brandon San Diege CA In Both G857 Bill Los Angeles CA In Both A123 John Austin TX In "1" Only B134 Susan Miami FL In "1" Only D555 Mark Phoenix AZ In "1" Only

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

#### MickG

##### MrExcel MVP
Try this for Results on sheet2.
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG07Dec40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Ac = 1 To 5 [COLOR="Navy"]Step[/COLOR] 4
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, Ac), Cells(Rows.Count, Ac).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]With[/COLOR] Application
Txt = Join(.Transpose(.Transpose(Dn.Resize(, 4))), ",")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not Dic.Exists(Txt) [COLOR="Navy"]Then[/COLOR]
Col = IIf(Ac = 1, 1, 2)
Dic.Add Txt, Array(Dn.Value, Dn.Offset(, 1).Value, Dn.Offset(, 2).Value, Dn.Offset(, 3).Value, "In """ & Col & """ Only")
[COLOR="Navy"]Else[/COLOR]
Q = Dic(Txt)
Q(4) = "In Both"
Dic(Txt) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
.Range("A1").Resize(, 5).Value = Array("Match No", "Match Name", "Match City", "Match State", "Match Result")
.Range("A2").Resize(Dic.Count, 5) = Application.Transpose(Application.Transpose(Dic.items))
[COLOR="Navy"]With[/COLOR] .Range("A1").Resize(Dic.Count + 1, 5)
.Borders.Weight = 2
.Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

#### ExcelMercy

##### Board Regular
Done it again! You're awesome

Thanks Mick!

You're welcome

Replies
5
Views
6K

1,191,723
Messages
5,988,309
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back