Extracting matched observation and its corresponding row

Closed
Abhi88 - Feb 5, 2016 at 04:18 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 9, 2016 at 11:47 AM
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.

Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 9, 2016 at 11:47 AM
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