none
MS Excel question, Need Macro Code to compare two rows in two separate tabs and return select info from cells next to the researched cell. RRS feed

  • Question

  • This is regarding comparing rows in two tabs and copy/pasting few cells next to the researched item. Both tabs are in the same worksheet and based on below criteria.


    First Tab name is "Data"

    Second Tab name is "Lookup"

    • In "Data" sheet search "status" in row 1, it can be in any column in row 1. (my column moves from reporting so need a search to locate 'status' in row 1 before comparing)
    • For each cell with a value under 'status', compare it to column "update" in "Lookup" tab (also "update" can be any column in row 1)
    • For every exact match, need to copy cell in 'Data' tab parallel to the matched item listed in row 1 under column "cars", "truck", "bus", "jeep", "RV" and paste it to the right of researched item which in this case is "Data" tab and "Status" column. 

    Please see below table for further clarification:

    "Data" Tab:

    cars
    Truck bus jeep Status RV
    012 013 014 015 New 017
    018 019 020 021 Old 023
    024 025 026 027 Dormant 029
    030 031 032 033 Code 034 035
    036 037 038 039 Code 040 041

    "Lookup" Tab:

    XXXX XXXX Update XXXX
    Old
    abc
    034
    new
    Dormant

    Expected Result:

    Status
    cars truck bus jeep RV
    New 012 013 014 015 017
    Old 018 019 020 021 023
    Dormant 024 025 026 027 029
    034 030 031 032 033 035




    Please note that 'code 040' is not listed in lookup tab so it is left out and copied.

    Hope this makes sense.

    Thank you!

    Tuesday, December 20, 2016 12:50 AM

Answers

  • Hi,

    A quick way is using WorksheetFunction.VLookup Method (Excel)

    Create a sheet named as "Result", then test the code below.

    Sub Demo() Application.ScreenUpdating = False Dim i As Integer i = Worksheets("Data").UsedRange.Columns.Count ' copy the columns "Status" into a new column A Worksheets("Data").Columns("A:A").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove With Worksheets("Data").UsedRange Set c = .Find("Status", LookIn:=xlValues) If Not c Is Nothing Then Worksheets("Data").Columns(c.Column).Copy Worksheets("Data").Paste Destination:=Worksheets("Data").Columns("A:A") End If End With ' copy the columns "Update" into sheets("Result") With Worksheets("Lookup").UsedRange Set c = .Find("Update", LookIn:=xlValues) If Not c Is Nothing Then Worksheets("Lookup").Columns(c.Column).Copy Worksheets("Lookup").Paste Destination:=Worksheets("Result").Columns("A:A") End If End With Application.CutCopyMode = False

    ' lookup the records and put it into sheets("Result") Worksheets("Result").Range("A1").Value = "Status" For Each cell In Worksheets("Result").UsedRange.Columns(1) For j = 1 To i cell.Offset(0, j).Value = Application.VLookup(cell.Value, Worksheets("Data").UsedRange, j + 1, False) Next j Next cell 'delete temp column A in sheets("Data") to recover Worksheets("Data").Columns("A:A").Delete

    'delete all the error cells in sheets("Result") For Each cell In Worksheets("Result").UsedRange If WorksheetFunction.IsError(cell) Then cell.EntireRow.Delete End If Next 'delete duplicate column "Status" in sheets("Result") With Worksheets("Result").UsedRange Set c = .Find("Status", LookIn:=xlValues) If Not c Is Nothing Then Worksheets("Result").Columns(c.Column).Delete End If End With Application.ScreenUpdating = True End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Marked as answer by Pete198 Friday, December 23, 2016 4:50 AM
    • Edited by Chenchen LiModerator Friday, December 23, 2016 7:52 AM Append comments
    Wednesday, December 21, 2016 6:57 AM
    Moderator
  • Hi,

    Which line cause the error?

    Please see the picture below. The code works fine for me and shows me the expected result.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pete198 Thursday, December 22, 2016 4:22 PM
    Thursday, December 22, 2016 2:57 AM
    Moderator
  • Hi,

    >>macro will look for 'status' row and compare it with 'lookup' sheet, 'update' column and put a filter on 'status' row and select items that match, with macro ending here.

     

    You may create a combobox for single selection or a listbox for multi selection. Then return the selected value and apply filters to the data.

    It depends on your requirement. I suggest you post a new thread and  clarify your requirement.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pete198 Thursday, December 29, 2016 12:39 AM
    Tuesday, December 27, 2016 3:24 AM
    Moderator

All replies

  • Hi,

    A quick way is using WorksheetFunction.VLookup Method (Excel)

    Create a sheet named as "Result", then test the code below.

    Sub Demo() Application.ScreenUpdating = False Dim i As Integer i = Worksheets("Data").UsedRange.Columns.Count ' copy the columns "Status" into a new column A Worksheets("Data").Columns("A:A").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove With Worksheets("Data").UsedRange Set c = .Find("Status", LookIn:=xlValues) If Not c Is Nothing Then Worksheets("Data").Columns(c.Column).Copy Worksheets("Data").Paste Destination:=Worksheets("Data").Columns("A:A") End If End With ' copy the columns "Update" into sheets("Result") With Worksheets("Lookup").UsedRange Set c = .Find("Update", LookIn:=xlValues) If Not c Is Nothing Then Worksheets("Lookup").Columns(c.Column).Copy Worksheets("Lookup").Paste Destination:=Worksheets("Result").Columns("A:A") End If End With Application.CutCopyMode = False

    ' lookup the records and put it into sheets("Result") Worksheets("Result").Range("A1").Value = "Status" For Each cell In Worksheets("Result").UsedRange.Columns(1) For j = 1 To i cell.Offset(0, j).Value = Application.VLookup(cell.Value, Worksheets("Data").UsedRange, j + 1, False) Next j Next cell 'delete temp column A in sheets("Data") to recover Worksheets("Data").Columns("A:A").Delete

    'delete all the error cells in sheets("Result") For Each cell In Worksheets("Result").UsedRange If WorksheetFunction.IsError(cell) Then cell.EntireRow.Delete End If Next 'delete duplicate column "Status" in sheets("Result") With Worksheets("Result").UsedRange Set c = .Find("Status", LookIn:=xlValues) If Not c Is Nothing Then Worksheets("Result").Columns(c.Column).Delete End If End With Application.ScreenUpdating = True End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Marked as answer by Pete198 Friday, December 23, 2016 4:50 AM
    • Edited by Chenchen LiModerator Friday, December 23, 2016 7:52 AM Append comments
    Wednesday, December 21, 2016 6:57 AM
    Moderator
  • Hi, 

    Thank you for replying. When i ran macro it gave an error "runtime error '9' sub script is out of range", then it executes with few things i noticed that need to be changed:

    • It would be nice if the output is placed to the right of 'Status' cell or in a different tab. The result displays two status rows.
    • Also, the lookup should not paste row for code 040 since it is not one of the items listed in lookup tab, column update.

    Here is the outcome of this code:

    Status cars Truck bus jeep Status RV
    New 12 13 14 15 New 17
    Old 18 19 20 21 Old 23
    Dormant 24 25 26 27 Dormant 29
    Code 034 30 31 32 33 Code 034 35
    Code 040 36 37 38 39 Code 040 41

    Thank you!



    • Edited by Pete198 Thursday, December 22, 2016 1:37 AM
    Thursday, December 22, 2016 1:36 AM
  • Hi,

    Which line cause the error?

    Please see the picture below. The code works fine for me and shows me the expected result.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pete198 Thursday, December 22, 2016 4:22 PM
    Thursday, December 22, 2016 2:57 AM
    Moderator
  • Hi, Thank you, it worked for me second time when i started sheet from scratch something i was not doing right the first time. Appreciate your help. Last request regarding this question. Possible you can break this code in two modules.

    • Module 1: It looks up list in 'lookup' tab 'update' column compares and sorts 'data tab' 'status' column to show items listed in 'lookup' tab 'update' column. Basically lookup, compare and sort 'data' sheet by 'status' column.
    • Module 2: Remaining analysis, copying and pasting result.

    Once again. Thank you very much!

    Regards,

    Pete


    • Edited by Pete198 Friday, December 23, 2016 5:21 AM
    Friday, December 23, 2016 5:19 AM
  • Hi,

    Why do you want to split it?

    I am using Vlookup function to directly look up and then input the value into the result sheet.

    cell.Offset(0, J).Value = Application.VLookup(cell.Value, Worksheets("Data").UsedRange, J + 1, False)

    The rest parts are searching column "Status" and "Update" because we have no idea which column it is in. Please see the comments in my first post.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 23, 2016 7:50 AM
    Moderator
  • Hi, after reading your comments i understand the logic behind code, and don't want to change it. However, i still need a separate module to replace existing module in another macro. The one i have adds filter and selects items that are created by recording. i want to replace it with another macro that compares to other list and adds filter and selects items that exist in other list. for example, in data sheet, macro will look for 'status' row and compare it with 'lookup' sheet, 'update' column and put a filter on 'status' row and select items that match, with macro ending here.

    The macro goes on and on after this module thought it would be easy to just replace  with something that have the capability to compare to a list.

    Hope above makes sense :)

    Thank you!

    -Pete


    • Edited by Pete198 Friday, December 23, 2016 10:16 AM
    Friday, December 23, 2016 10:15 AM
  • Hi,

    >>macro will look for 'status' row and compare it with 'lookup' sheet, 'update' column and put a filter on 'status' row and select items that match, with macro ending here.

     

    You may create a combobox for single selection or a listbox for multi selection. Then return the selected value and apply filters to the data.

    It depends on your requirement. I suggest you post a new thread and  clarify your requirement.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pete198 Thursday, December 29, 2016 12:39 AM
    Tuesday, December 27, 2016 3:24 AM
    Moderator