Extracting matched observation and its corresponding row [Closed]

Report
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
Hello,
I have excel sheet containing around 100K observations (rows).
There is one column say "A" containing some IDs and each ID is having 10 parameter values (say in columns "B" to "K"). Now, I have another column say "AA" containing IDs (around 1K) which I'm interested in filtering from those 100K. Is there any way so that I can filter these IDs and retain their corresponding parameter values in one go?
Thank you in advance.

1 reply

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Abhi88,

In the code below the source sheet is refered to as Sheet1 and the destination sheet as Sheet2.

Make sure both sheets are present in your workbook and named accordingly. Or adjust the sheet names in the code (Sheet1 is found on code line 4, Sheet2 is found on code line 9).

Here is the code:
Sub RunMe()
Dim mCell As Range

Sheets("Sheet1").Select

For Each cell In Range("AA2:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
    Set mCell = Columns("A:A").Find(cell.Value)
    Range(Range("A" & mCell.Row), Range("K" & mCell.Row)).Copy _
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell
End Sub


Best regards,
Trowa