Function - Merge / Matching Question

ExcelMercy

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
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_1Name_1Num_2Name_2
A111JimA111Jim
A123JohnA111Cory
B134SusanA124Marcus
B134LoriB121Donald
C786BillyB134Mark
D555JohnB134Lori
D555MarkC786Billy
F121KimC786Donny
F124BonnieD124Thomas
F475BrandonD124Austin
G857BillD124Stacy
D555John
D558Justin
F121Kim
F124Bonnie
F475Brandon
G857Bill

<tbody>
</tbody>


Example Output:

Match_NumMatch_NameMATCH RESULTS
A111JimIn Both
A111CoryIn "2" Only
A124MarcusIn "2" Only
B121DonaldIn "2" Only
B134MarkIn "2" Only
B134LoriIn Both
C786BillyIn Both
C786DonnyIn "2" Only
D124ThomasIn "2" Only
D124AustinIn "2" Only
D124StacyIn "2" Only
D555JohnIn Both
D558JustinIn "2" Only
F121KimIn Both
F124BonnieIn Both
F475BrandonIn Both
G857BillIn Both
A123JohnIn "1" Only
B134SusanIn "1" Only
D555MarkIn "1" Only

<tbody>
</tbody>



Any help would be greatly appreciated!
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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
 
Upvote 0
MickG said:
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
Click to expand...

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_1Name_1City_1State_1Num_2Name_2City_2State_2
A111JimNashvilleTNA111JimNashvilleTN
A123JohnAustinTXA111CorySeattleWA
B134SusanMiamiFLA124MarcusDetroitMI
B134LoriNew York CityNYB121DonaldChicagoIL
C786BillyChicagoILB134MarkSan AntonioTX
D555JohnHoustonTXB134LoriNew York CityNY
D555MarkPhoenixAZC786BillyChicagoIL
F121KimJacksonvilleFLC786DonnyHoustonTX
F124BonnieSan AntonioTXD124ThomasHoustonTX
F475BrandonSan DiegeCAD124AustinLos AngelesCA
G857BillLos AngelesCAD124StacyChicagoIL
D555JohnHoustonTX
D558JustinSan DiegeCA
F121KimJacksonvilleFL
F124BonnieSan AntonioTX
F475BrandonSan DiegeCA
G857BillLos AngelesCA

<tbody>
</tbody>


Example Results:
Match_NumMatch_NameMatch_CityMatch_StateMATCH RESULTS
A111JimNashvilleTNIn Both
A111CorySeattleWAIn "2" Only
A124MarcusDetroitMIIn "2" Only
B121DonaldChicagoILIn "2" Only
B134MarkSan AntonioTXIn "2" Only
B134LoriNew York CityNYIn Both
C786BillyChicagoILIn Both
C786DonnyHoustonTXIn "2" Only
D124ThomasHoustonTXIn "2" Only
D124AustinLos AngelesCAIn "2" Only
D124StacyChicagoILIn "2" Only
D555JohnHoustonTXIn Both
D558JustinSan DiegeCAIn "2" Only
F121KimJacksonvilleFLIn Both
F124BonnieSan AntonioTXIn Both
F475BrandonSan DiegeCAIn Both
G857BillLos AngelesCAIn Both
A123JohnAustinTXIn "1" Only
B134SusanMiamiFLIn "1" Only
D555MarkPhoenixAZIn "1" Only

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
help on SUM
Replies
5
Views
6K
mvptomlinson
M

Forum statistics

Threads
1,214,590
Messages
6,120,421
Members
448,961
Latest member
nzskater

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top