none
match 2 columns and return only the rows that were matched RRS feed

  • Question

  • i have a table with 1M recoreds and a list of id's.

    i need to keep from the big list only the rows that matvh this id:

    for example:

    this is the table with the list on the side

    this is the result i want

    thanks

    


    • Edited by tom_herman Tuesday, July 19, 2016 1:04 PM
    Tuesday, July 19, 2016 1:04 PM

Answers

  • Hi tom_herman,

    you can try to use the code below.

    Sub test()
     Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer
     Dim lastRow As Long
        For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
            Set rng1 = Sheets("Sheet1").Range("A" & i)
            For j = 1 To Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
                Set rng2 = Sheets("Sheet1").Range("G" & j)
                Set rngName = Sheets("Sheet1").Range("A" & j)
                If rng1.Value = rng2.Value Then
                lastRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                    rng1.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & lastRow)
                End If
    
            Set rng2 = Nothing
        Next j
        Set rng1 = Nothing
     Next i
    End Sub
    

    you will get output like below.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 20, 2016 2:34 AM
    Moderator

All replies

  • Use a column of formulas - let's say that you sheet is arranged as shown, with IDs in column A, and your sublist in column G.  In E2, enter

    =IF(ISERROR(MATCH(A2,G:G,FALSE)),"","Keep me")

    copy down to match your list - then filter to show only "Keep me" values, and copy them elsewhere to save the list.

    Tuesday, July 19, 2016 3:20 PM
  • Hi tom_herman,

    you can try to use the code below.

    Sub test()
     Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer
     Dim lastRow As Long
        For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
            Set rng1 = Sheets("Sheet1").Range("A" & i)
            For j = 1 To Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
                Set rng2 = Sheets("Sheet1").Range("G" & j)
                Set rngName = Sheets("Sheet1").Range("A" & j)
                If rng1.Value = rng2.Value Then
                lastRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                    rng1.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & lastRow)
                End If
    
            Set rng2 = Nothing
        Next j
        Set rng1 = Nothing
     Next i
    End Sub
    

    you will get output like below.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 20, 2016 2:34 AM
    Moderator
  • Can you just do lookups? Create your output area formatted as a table and put lookups to pull back the correct data based on the ID you enter. If the output is formatted as a table, any time you add a new id in the left column, the formulas will be copied to the new row in the table.

    This is probably not a great solution if your output is going to have lots of rows but for a small project it will work.

    Wednesday, July 20, 2016 3:50 PM