"Matching 2 numbers in tables together"

marwan1

Board Regular
Joined
Mar 2, 2011
Messages
145
Hello every one,

I have two tables. One has long list of numbers, and the other one has lesser. I'd like to match the identical numbers in both tables. By the way the first table has 14600 cells and the short one has 1466 cells.


14247711424782
14247721424786
14247731424796
14247741424799
14247751424876
14247751424881
14247761424885
14247791424886
1424780
1424781
1424782
1424783
1424784
1424785
1424785
1424786
1424787
1424787
1424788
1424788
1424789
1424789
1424790
1424790
1424791
1424791
1424792
1424792
1424793
1424793
1424794
1424794
1424795
1424795
1424796
1424796
1424797
1424797
1424798
1424799
1424799
1424800
1424800
1424801
1424801
1424802
1424803
1424804
1424805
1424806
1424807
1424808
1424809
1424810
1424811
1424812
1424813
1424814
1424815
1424816
1424817
1424818
1424819
1424820
1424821
1424822
1424823
1424824
1424825
1424826
1424827
1424828
1424829
1424830
1424831
1424831
1424832
1424832
1424833
1424833
1424834
1424835
1424836
1424837
1424838
1424839
1424840
1424841
1424842
1424842
1424843
1424843
1424844
1424845
1424847
1424848
1424849
1424850
1424851
1424852
1424853
1424854
1424855
1424856
1424857
1424858
1424859
1424860
1424862
1424863
1424864
1424865
1424866
1424867
1424867
1424868
1424869
1424876
1424880
1424881
1424882
1424883
1424884
1424885
1424886

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

Thanks for your help.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
It just so happens I've created a macro for just this purpose.

Set up your headers like so:


A</SPAN>​

B</SPAN>​

C</SPAN>​

D</SPAN>​

E</SPAN>​

F</SPAN>​

G</SPAN>​

H</SPAN>​
List A</SPAN>
List B</SPAN>
In Both Lists</SPAN>
Only in List A</SPAN>
List A Duplicates</SPAN>
Only in List B</SPAN>
List B Duplicates</SPAN>

<TBODY>
</TBODY>

Code:
 Option Explicit
Sub compare()
Dim ListA As Range
Dim ListB As Range
Dim x As Integer
Dim MyCell As Range
For x = 2 To Range("A1048576").End(xlUp).Row
Set MyCell = Cells(x, 1)
'Check for Duplicates and add to duplicates list
If Application.CountIf(Range("A:A"), MyCell.Value) > 1 And Application.CountIf(Range("F:F"), MyCell.Value) = 0 Then
    Range("F1048576").End(xlUp).Offset(1) = MyCell.Value 'ListA Duplicate
End If
'Check for presence in ListB
If Application.CountIf(Range("B:B"), MyCell.Value) > 0 And Application.CountIf(Range("D:D"), MyCell.Value) = 0 Then
    Range("D1048576").End(xlUp).Offset(1) = MyCell.Value 'Both Lists
ElseIf Application.CountIf(Range("B:B"), MyCell.Value) = 0 And Application.CountIf(Range("E:E"), MyCell.Value) = 0 Then
    Range("E1048576").End(xlUp).Offset(1) = MyCell.Value 'ListA Only
End If
Next
For x = 2 To Range("B1048576").End(xlUp).Row
Set MyCell = Cells(x, 2)
'Check for Duplicates and add to duplicates list
If Application.CountIf(Range("B:B"), MyCell.Value) > 1 And Application.CountIf(Range("H:H"), MyCell.Value) = 0 Then
    Range("H1048576").End(xlUp).Offset(1) = MyCell.Value 'ListB Duplicate
End If
'Check for presence in ListA
If Application.CountIf(Range("A:A"), MyCell.Value) = 0 And Application.CountIf(Range("G:G"), MyCell.Value) = 0 Then
    Range("G1048576").End(xlUp).Offset(1) = MyCell.Value 'ListB Only
End If
Next
End Sub
 
Upvote 0
It just so happens I've created a macro for just this purpose.

Set up your headers like so:


A</SPAN>​

B</SPAN>​

C</SPAN>​

D</SPAN>​

E</SPAN>​

F</SPAN>​

G</SPAN>​

H</SPAN>​
List A</SPAN>
List B</SPAN>
In Both Lists</SPAN>
Only in List A</SPAN>
List A Duplicates</SPAN>
Only in List B</SPAN>
List B Duplicates</SPAN>

<TBODY>
</TBODY>

Code:
 Option Explicit
Sub compare()
Dim ListA As Range
Dim ListB As Range
Dim x As Integer
Dim MyCell As Range
For x = 2 To Range("A1048576").End(xlUp).Row
Set MyCell = Cells(x, 1)
'Check for Duplicates and add to duplicates list
If Application.CountIf(Range("A:A"), MyCell.Value) > 1 And Application.CountIf(Range("F:F"), MyCell.Value) = 0 Then
    Range("F1048576").End(xlUp).Offset(1) = MyCell.Value 'ListA Duplicate
End If
'Check for presence in ListB
If Application.CountIf(Range("B:B"), MyCell.Value) > 0 And Application.CountIf(Range("D:D"), MyCell.Value) = 0 Then
    Range("D1048576").End(xlUp).Offset(1) = MyCell.Value 'Both Lists
ElseIf Application.CountIf(Range("B:B"), MyCell.Value) = 0 And Application.CountIf(Range("E:E"), MyCell.Value) = 0 Then
    Range("E1048576").End(xlUp).Offset(1) = MyCell.Value 'ListA Only
End If
Next
For x = 2 To Range("B1048576").End(xlUp).Row
Set MyCell = Cells(x, 2)
'Check for Duplicates and add to duplicates list
If Application.CountIf(Range("B:B"), MyCell.Value) > 1 And Application.CountIf(Range("H:H"), MyCell.Value) = 0 Then
    Range("H1048576").End(xlUp).Offset(1) = MyCell.Value 'ListB Duplicate
End If
'Check for presence in ListA
If Application.CountIf(Range("A:A"), MyCell.Value) = 0 And Application.CountIf(Range("G:G"), MyCell.Value) = 0 Then
    Range("G1048576").End(xlUp).Offset(1) = MyCell.Value 'ListB Only
End If
Next
End Sub

I'm sorry but I'm not that familiar with macros yet.
Thanks for your help Juglaz.
 
Upvote 0
Smitty, I used the Vlookup and tried to built it in within the array (A1:A125) using the conditional format but always highlighting the first cell only! I wrote: =VLOOKUP(C1,A1:A125,1), where C1 is the data validation for the small table.

Akyurek, your formula gives me number 1 all the way!


Thanks guys for your help.
 
Upvote 0
Smitty, I used the Vlookup and tried to built it in within the array (A1:A125) using the conditional format but always highlighting the first cell only! I wrote: =VLOOKUP(C1,A1:A125,1), where C1 is the data validation for the small table.

Akyurek, your formula gives me number 1 all the way!


Thanks guys for your help.

You should get 1 if an item from the shorter list is in the longer list. You need to track down an item for which a 1 is unjustified for further diagnosis.
 
Upvote 0
Worked perfectly Akyurek!

But I wished the item in the long list also gets marked. Because what happens here is your formula only marks 1 the number that appears on the short list which has a corresponding item in the longer ones and leave the number in the long list unmarked which some times repeated more than ones.

Thanks a lot for your help.
 
Upvote 0
Worked perfectly Akyurek!

But I wished the item in the long list also gets marked. Because what happens here is your formula only marks 1 the number that appears on the short list which has a corresponding item in the longer ones and leave the number in the long list unmarked which some times repeated more than ones.

Thanks a lot for your help.

The sample is only partially shown in the exhibit below...


XX in YYY in X
1424771014247821
1424772014247861
1424773014247961
1424774014247991
1424775014248761
1424775014248811
1424776014248851
1424779014248861
14247800
14247810
14247821
14247830
14247840
14247850
14247850
14247861
14247870
14247870
14247880
14247880

<COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2986" width=84><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3384" width=95><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

B2, copied down:

=ISNUMBER(MATCH($A2,C:C,0))+0

If C:C is sorted in ascending order, change the foregoing formula to:

=ISNUMBER(MATCH($A2,C:C,1))+0

D2, copied down:

=ISNUMBER(MATCH($C2,A:A,0))+0

If A:A is sorted in ascending order, change the foregoing formula to:

=ISNUMBER(MATCH($C2,A:A,1))+0
 
Upvote 0
Why not use VLOOKUP from the smaller list to the bigger one?
1.jpg
2.jpg

3.jpg

4.jpg

If you insist on VLOOKUP...

=IFERROR((VLOOKUP($A2,C:C,1,0)=$A2)+0,0)

would be equivalent to:

=ISNUMBER(MATCH($A2,C:C,0))+0

Or, if the output is acceptable...

=VLOOKUP($A2,C:C,1,0)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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