Thanks:  0
Likes:  0

# Thread: "Matching 2 numbers in tables together"

1. ## "Matching 2 numbers in tables together"

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.

 1424771 1424782 1424772 1424786 1424773 1424796 1424774 1424799 1424775 1424876 1424775 1424881 1424776 1424885 1424779 1424886 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

2. ## Re: "Matching 2 numbers in tables together"

It just so happens I've created a macro for just this purpose.

 A B C D E F G H List A List B In Both Lists Only in List A List A Duplicates Only in List B List B Duplicates

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```

3. ## Re: "Matching 2 numbers in tables together"

Originally Posted by Juglaz
It just so happens I've created a macro for just this purpose.

 A B C D E F G H List A List B In Both Lists Only in List A List A Duplicates Only in List B List B Duplicates

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.

4. ## Re: "Matching 2 numbers in tables together"

Why not use VLOOKUP from the smaller list to the bigger one?

5. ## Re: "Matching 2 numbers in tables together"

C2, copied down:

=ISNUMBER(MATCH(B2,A:A,0))+0

A hit is 1, otherwise 0.

Is this what you have in mind?

6. ## Re: "Matching 2 numbers in tables together"

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!

7. ## Re: "Matching 2 numbers in tables together"

Originally Posted by marwan1
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!

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.

8. ## Re: "Matching 2 numbers in tables together"

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.

9. ## Re: "Matching 2 numbers in tables together"

Originally Posted by marwan1
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...

 X X in Y Y Y in X 1424771 0 1424782 1 1424772 0 1424786 1 1424773 0 1424796 1 1424774 0 1424799 1 1424775 0 1424876 1 1424775 0 1424881 1 1424776 0 1424885 1 1424779 0 1424886 1 1424780 0 1424781 0 1424782 1 1424783 0 1424784 0 1424785 0 1424785 0 1424786 1 1424787 0 1424787 0 1424788 0 1424788 0

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

10. ## Re: "Matching 2 numbers in tables together"

Originally Posted by Iredhne
Why not use VLOOKUP from the smaller list to the bigger one?

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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•