Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: "Matching 2 numbers in tables together"

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Location
    Out there!
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "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

    Thanks for your help.

  2. #2
    Board Regular Juglaz's Avatar
    Join Date
    Jul 2013
    Location
    Michigan
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Matching 2 numbers in tables together"

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

    Set up your headers like so:

    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. #3
    Board Regular
    Join Date
    Mar 2011
    Location
    Out there!
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Matching 2 numbers in tables together"

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

    Set up your headers like so:

    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.
    Thanks for your help Juglaz.

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: "Matching 2 numbers in tables together"

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

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default 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?
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular
    Join Date
    Mar 2011
    Location
    Out there!
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!


    Thanks guys for your help.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: "Matching 2 numbers in tables together"

    Quote Originally Posted by marwan1 View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    Board Regular
    Join Date
    Mar 2011
    Location
    Out there!
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: "Matching 2 numbers in tables together"

    Quote Originally Posted by marwan1 View Post
    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
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: "Matching 2 numbers in tables together"

    Quote Originally Posted by Iredhne View Post
    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)
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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