Compare values between 2 tables

M

Michael1974

New member
Joined
Mar 31, 2015
Messages
49
Reaction score
0
Points
0
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
 

Attachments

  • Book1.xls
    82.5 KB · Views: 13
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
 

Attachments

  • Book1 v1.xls
    150 KB · Views: 19
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
 
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
 

Attachments

  • BookTest.xls
    38.5 KB · Views: 12
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.
 
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.
 
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.
 
Hi Michael

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

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

Attachments

  • BookTest v1.1.xls
    79 KB · Views: 11
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
 
Jaslake,

For information, I am able to get the look up function to work. But my only remaining challenge is to get the code that will copy and paste all the unmatched values. Basically, the 3rd table (from Column AA to Column AD) will tell us what are the unmatched values between Table 2 and Table 1. All is needed is to get those unmatched values from Table 2 to be copied and pasted to sheet2. I have attached a copy of the spreadsheet called Trial LookUp. Please not that the original sheet is way bigger but the layout is identical. Please run the code. It would be great if you could help getting the code that will copy and paste all the unmatched values from Table 2 to sheet2.

Thanks
 

Attachments

  • Trial LookUp.xls
    54.5 KB · Views: 9
Hi Michael

Please look at the Code for Sub LookUp. I've modified some Lines of Code and have added some Lines of Code.

Look at the added Lines of Code in particular and you'll see that Sub LookUp CALLS Sub Mismatch
Code: 
 '##################
   'Added these lines of Code
   Sheet1.Columns("AA:AD").Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                                   ReplaceFormat:=False
   Call MisMatch
   '##################

To run the Code use the Keyboard shortcut CTRL + x. This will run Sub Lookup which in turn CALLS Sub Mismatch.

Test the Code on the attached.

Included in the Workbook are Worksheets that can be deleted as testing proves positive.
  • I've included Sheets jaslake_output...this is what I get in running the Code...your output will be on Sheet2.
  • I've included Sheets Original_Data such that you can reinstate the Original Data to Sheet1 for testing.

Let me know...
 

Attachments

  • Trial LookUp v1.xls
    87 KB · Views: 4
Good morning Jaslake,

Thanks a lot. I still have problem with the Sub MisMatch () procedure. In addition to the matched values, it also shows the unmatched values which is not supposed to do.
 
Jaslake,

Just to be clear, from the sheet you sent to me it works fine. However, when I use the macro in the initial sheet (which is way bigger), I have the problem that I have just mentioned with the Sub MisMatch () Procedure: some of the unmatched values appear on sheet2 which is not supposed to do.

Thanks
 
Hi Michael

I'd have no way of knowing without seeing the offending File. This would suggest that the Structure of the offending File is different from your sample File
Just to be clear, from the sheet you sent to me it works fine. However, when I use the macro in the initial sheet (which is way bigger), I have the problem that I have just mentioned with the Sub MisMatch () Procedure: some of the unmatched values appear on sheet2 which is not supposed to do.
Click to expand...
 
Is there a way for me to send this sheet to you? I am quite new to this forum. Can I send it to you by e-mail? because of the size of the original sheet, I can't attach it through this forum.
 
Jaslake,

Please see in the attached the Zipped Version of the Original file. After running the macro, go to sheet2 where we are supposed to have the unmatched values (Values that are normally not matching the ones in Table 1 composed from Column A To P). On Sheet2 you have matched and unmatched values which is not supposed be.

Thanks
 

Attachments

  • Trial Matched & Unamatched.zip
    343.6 KB · Views: 9
Hi Michael
These items do not match...the Quantities are different...what am I missing?

Code: 
       -------A-------- ---B---- --C--- ---D---- E F -------G-------- ---H---- --I--- ---J----
   1   UniqueID         Item #   OnHand Location     UniqueID         Item #   OnHand Location
   2   003638200-S2E02B  3638200      0 S2E02B                                                
   3   008266001-S8A02A  8266001      2 S8A02A                                                
   4   010228183-S8C01A 10228183      4 S8C01A                                                
   5   011354315-S8D04A 11354315      1 S8D04A                                                
   6   013386649-S1C02A 13386649      0 S1C02A                                                
   7   013977557-02C01A 13977557      2 02C01A                                                
   8   013977557-S8B01A 13977557      3 S8B01A                                                
   9   013988871-B2C01A 13988871      1 B2C01A                                                
  10   014118650-S7A04A 14118650      3 S7A04A                                                
  11   014598465-S6B01A 14598465      4 S6B01A                                                
  12   014655994-S11DK3 14655994      0 S11DK3                                                
  13   014703235-S8A04A 14703235      1 S8A04A                                                
  14   014991272-B2B01A 14991272      1 B2B01A                                                
  15   015058119-S8C02A 15058119      1 S8C02A                                                
  16   015087385-01C01I 15087385     12 01C01I       015087385-01C01I 15087385      8 01C01I  
  17   015087385-B1A08E 15087385      4 B1A08E                                                
  18   015087385-S2A02C 15087385      0 S2A02C       015087385-S2A02C 15087385      5 S2A02C  
  19   015087450-B1A02A 15087450      2 B1A02A                                                
  20   015172881-S2G02B 15172881      0 S2G02B                                                
  21   015190748-S7TOP1 15190748      3 S7TOP1       015190748-S7TOP1 15190748      4 S7TOP1  
  22   015198797-B1B02B 15198797      1 B1B02B                                                
  23   015205798-B2B01A 15205798      1 B2B01A                                                
  24   015206572-B1B06A 15206572      4 B1B06A                                                
  25   015222270-B2D01A 15222270      2 B2D01A                                                
  26   015222270-S8B02A 15222270      1 S8B02A                                                
  27   015222271-B2D01A 15222271      2 B2D01A                                                
  28   015222271-S8A01A 15222271      2 S8A01A                                                
  29   015230931-B1A05B 15230931      8 B1A05B                                                
  30   015230942-01A01B 15230942     18 01A01B       015230942-01A01B 15230942     16 01A01B
 
Jaslake,

After reviewing the information again, you are right. Apparently all the info on sheet does not match which is what that sheet is intended for. I misread the information on sheet 2. I am sorry. I am going to go over your code to learn and understand how you were able to get this to work. I will let you know if something goes wrong. In the meantime I thank you very much for your help. What are the hours, you usually can be reached online, on that forum?
 
You must log in or register to reply here.
Back
Top