Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 31

Thread: Compare values between 2 tables

  1. #1

    Compare values between 2 tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    Please see in the attached an excel Spreadsheet where there are 2 tables in sheet 1. Each table has 4 columns. What I am trying to do is to compare the values between the 2 tables and any unmatched values must be copied and pasted in another sheet (sheet 2).

    Does anyone have any suggestion? If my question is not clear. Please let me know.

    Thanks

    Michael
    Attached Files Attached Files

  2. #2
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Michael

    Welcome to the Forum!!

    Try this Code in the attached
    Code:
    Option Explicit
     Sub MisMatch()
        Dim ws As Worksheet, ws1 As Worksheet
        Dim Rng As Range, cel As Range
        Dim LR As Long, LR1 As Long
        Dim Headers()    As Variant
        Set ws = Sheets("Sheet1")
        Set ws1 = Sheets("Sheet2")
        Application.ScreenUpdating = False
        With ws
           LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
           Headers = .Range("A1").Resize(1, 4).Value
           With ws1
              .Cells.Clear
              .Range("A1").Resize(1, 4).Value = Headers
              .Range("G1").Resize(1, 4).Value = Headers
           End With
           Set Rng = .Range(.Cells(2, "A"), .Cells(LR, "A"))
           For Each cel In Rng
              .Cells(cel.Row, "L").Value = .Cells(cel.Row, "A") & Cells(cel.Row, "B") & Cells(cel.Row, "C") & Cells(cel.Row, "D")
              .Cells(cel.Row, "M").Value = .Cells(cel.Row, "G") & Cells(cel.Row, "H") & Cells(cel.Row, "I") & Cells(cel.Row, "J")
           Next cel
           Set Rng = .Range(.Cells(2, "L"), .Cells(LR, "L"))
           For Each cel In Rng
              If Not cel.Value = cel.Offset(0, 1).Value Then
                 With ws1
                    LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                                      SearchDirection:=xlPrevious).Row + 1
                    .Range("A" & LR1).Resize(1, 4).Value = ws.Cells(cel.Row, "A").Resize(1, 4).Value
                    .Range("G" & LR1).Resize(1, 4).Value = ws.Cells(cel.Row, "G").Resize(1, 4).Value
                 End With
              End If
           Next cel
        End With
        With ws
           .Columns("L:M").Delete
        End With
        Application.ScreenUpdating = True
     End Sub
    Attached Files Attached Files

  3. #3
    Jaslake,

    Great thanks!!! It was exactly what was looking for. I am going to integrate this code with another one that is supposed to run a VLookup function to check all the matched values. If I am having issues combining those 2 codes, I will let you know.

    Thanks a lot

  4. #4
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    You're welcome...glad I could help.

  5. #5

    Compare Values Between 2 Tables

    jaslake,

    I have integrated your code with mine and I can't make it to work properly. My goal is to use a VBA VLookup that will lookup for the matched values. Once I run the VLookup macro, a Unique ID will be created in Table 1 (Column A To O, inserting a column called UniqueID in column A), Table 2 (Column U To X, inserting a column called UniqueID in column AA). So far, that part of the code works.

    Then, I want with the codes that you pasted being able to copy and paste the unmatched values in Sheet 2. This is where the issue is. I think I don't really understand your code although it does work terrific. I have attached the a spreasheet called BooKTest.

    Can you please help find a solution and understand your code portion? Thanks in advance
    Code:
    Sub ADDCLM()
         'put all the variable definitions at top for easy reference
        Dim Table1 As Range
        Dim Table2 As Range
        Dim lastRow As Long
         
         
         
         
        Application.ScreenUpdating = False
         
         
        Range("A1").EntireColumn.Insert
        Range("U1").EntireColumn.Insert
        Range("A1").Value = "UniqueID"
        Range("U1").Value = Range("A1").Value
        Range("A21").Value = Range("A1").Value
         
         
         
         
        lastRow = Cells(Rows.Count, "B").End(xlUp).Row
        Range("A2:A" & lastRow).Formula = "=B2&""-""&N2"
        Range("U2:U" & lastRow).Formula = "=A2"
         
         
        Set Table1 = Sheet1.Range("A2:A292") ' Employee_ID Column from Employee table
        Set Table2 = Sheet1.Range("U2:X374") ' Range of Employee Table 1
         
         
         
         
         
         
        With Sheet1.Range("AA2").Resize(Table1.Rows.Count)
             'Apply a worksheet formula to all cells in one shot
            .Formula = "=VLOOKUP(A2," & Table2.Address & ",1,FALSE)"
            .Copy
            .PasteSpecial xlPasteValues
            
        End With
        
        
         With Sheet1.Range("AB2")
        
        Range("AB2:AB" & lastRow).Formula = "=VLookup(A2, " & Table2.Address & ",2,FALSE)"
        
        .Copy
        
        .PasteSpecial xlPasteValues
        
        End With
        
        
        
        With Sheet1.Range("AC2")
        
        Range("AC2:AC" & lastRow).Formula = "=VLookup(A2, " & Table2.Address & ",3,FALSE)"
        
        .Copy
        
        .PasteSpecial xlPasteValues
        
        End With
        
        
         With Sheet1.Range("AD2")
        
        Range("AD2:AD" & lastRow).Formula = "=VLookup(A2, " & Table2.Address & ",4,FALSE)"
        
        .Copy
        
        .PasteSpecial xlPasteValues
        
        End With
        
        Application.CutCopyMode = False
        
        
        Dim ws As Worksheet, ws1 As Worksheet
       Dim Rng As Range, cel As Range
       Dim LR As Long, LR1 As Long
       Dim Headers()    As Variant
       Set ws = Sheets("Sheet1")
       Set ws1 = Sheets("Sheet2")
       Application.ScreenUpdating = False
       With ws
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious).Row
          Headers = .Range("U1").Resize(1, 4).Value
          With ws1
             .Cells.Clear
             .Range("A1").Resize(1, 4).Value = Headers
             .Range("G1").Resize(1, 4).Value = Headers
          End With
          Set Rng = .Range(.Cells(2, "A"), .Cells(LR, "A"))
          For Each cel In Rng
             .Cells(cel.Row, "AF").Value = .Cells(cel.Row, "U") & Cells(cel.Row, "V") & Cells(cel.Row, "W") & Cells(cel.Row, "X")
             .Cells(cel.Row, "AG").Value = .Cells(cel.Row, "AA") & Cells(cel.Row, "AB") & Cells(cel.Row, "AC") & Cells(cel.Row, "AD")
          Next cel
          Set Rng = .Range(.Cells(2, "AF"), .Cells(LR, "AF"))
          For Each cel In Rng
             If Not cel.Value = cel.Offset(0, 1).Value Then
                With ws1
                   LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                                     SearchDirection:=xlPrevious).Row + 1
                   .Range("A" & LR1).Resize(1, 4).Value = ws.Cells(cel.Row, "A").Resize(1, 4).Value
                   .Range("G" & LR1).Resize(1, 4).Value = ws.Cells(cel.Row, "G").Resize(1, 4).Value
                End With
             End If
          Next cel
       End With
       With ws
          .Columns("AF:AG").Delete
       End With
       Application.ScreenUpdating = True
        
        
        
        
        Application.ScreenUpdating = True
        MsgBox "Done"
         
         
    End Sub
    Attached Files Attached Files

  6. #6
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Michael

    Please attach a File that's of the same structure as your actual File. It should be sanitized of confidential information. The attachment should include any Code you're currently using, whether the Code works or not.

    You should include a description of what it is you wish to do. A before Worksheet and an after Worksheet will be particularly helpful.

  7. #7
    Jaslake,

    I did attach a file of the same structure of the actual file that does not contain any confidential info. I had to remove some information due to the initial size of the spreadsheet. Also, I have already described what was needed. The attached file BookTest is the file before running any Macro. What is really needed is to run a VBA VLookup which will tell us whether all the information in Table 3 matches Table 2. And all the values that do not match must be copied from Table 2 (Columns U To X) and pasted to Sheet 2.

  8. #8
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Michael

    I was misreading your Code...thought you had some Named Ranges that were not included in the Workbook. Sorry, let me look at it again.

  9. #9
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Michael

    I don't seem to have a Code Tag Icon...

    Try the Code in the attached...seems to work...let me know.
    Attached Files Attached Files

  10. #10
    Good morning Jaslake,

    When I run the first Macro, I have the error message: Compile Error Variable not defined. And when I run the second Macro, I have another error message: "Type mismatch".

    To be clear, the goal is to get one macro to run it all. Once I run the macro, the following must happen:

    - Insert in column A and U, a column called UniqueID (which is the concatenate value between Item # and location)

    - A VlookUp must retrieve info regarding the item #, the OnHand Qty and the location (lookup value start at A2, the lookup array is table 2 ). The values retried appeared in a third column

    - Lastly the unmatched values must appear in sheet2

    Thanks

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

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