Help with sorting through order data

[DannyBoye30]

I’m trying to figure out how to filter 100k rows of data pulled in from a data connection.
I’m trying to filter by part #s, but each part # could have anywhere from 1 order to 1000. So I’m trying to find a way that will return all orders for given part numbers.
- sheet 1 is the data pulled in
- sheet 2 is the part numbers I’m interested in
- sheet 3 is the filtered orders and the other data in the row

I’ll include an example file of what I’m trying to do. I got something close with Vlookup and a filter but not quite what I’m looking for. I believe I need a VBA maco or something to do what I need, but I’m open to suggestions. Any help would be appreciated.

 

[Akuini]

@DannyBoye30, welcome to the Forum.

I’ll include an example file of what I’m trying to do.

Could you please upload a sample workbook to a file-sharing site like Dropbox or Google Drive and share the link here? Also, ensure that the link is accessible to anyone. If there is sensitive data, please replace it with representative dummy data. And please also provide the expected results.
Question:
1. Does the data only have 2 columns?

 

[Fuji]

Assuming 123-457 was 123-47 in sheet 2.
Sheet names are from your picture, so need to be checked.

Sub test()
    Dim r As Range
    Sheets("filtered results").[a1].CurrentRegion.Columns("a:b").ClearContents
    With Sheets("data pull").[a1].CurrentRegion
        .Range("a1:b1").Copy Sheets("filtered results").[a1]
        Set r = .Offset(, .Columns.Count + 2).Range("a1:a2")
        r(2).Formula = "=isnumber(match(a2,'part #s to pull'!a:a,0))"
        .AdvancedFilter 2, r, Sheets("filtered results").[a1:b1]
        r.Clear
    End With
End Sub

 

[DannyBoye30]

Thank you for the quick reply’s!
Here is an example sheet link. The output im looking for will be in sheet 3.

example.xlsx

docs.google.com

Akuni to answer your question, it’s supposed to have more than 2 columns, but the amount will vary between 5-15 so it needs to copy the whole row into the “filtered results” sheet.

The code Fuji wrote does most of what I need. It’s just missing the ability to pull the whole row into the results tab.
Thanks again for your help, this is really appreciated.

 

[DannyBoye30]

It would also be helpful if the order of the filtered part#s matched the order that they are put in on the 2nd sheet.

Thank you for the quick reply’s!
Here is an example sheet link. The output im looking for will be in sheet 3.

example.xlsx

docs.google.com

Akuni to answer your question, it’s supposed to have more than 2 columns, but the amount will vary between 5-15 so it needs to copy the whole row into the “filtered results” sheet.

The code Fuji wrote does most of what I need. It’s just missing the ability to pull the whole row into the results tab.
Thanks again for your help, this is really appreciated.

 

[Fuji]

Sub test()
    Dim a, e
    a = Filter(Sheets("Part #s to filter").[transpose(if(a2:a10000<>"",a2:a10000))], False, 0)
    Sheets("filtered results").[a1].CurrentRegion.Offset(1).ClearContents
    With Sheets("data pull").[a1].CurrentRegion
        .Parent.AutoFilterMode = False
        For Each e In a
            .AutoFilter 1, e
            .Offset(1).Copy Sheets("filtered results").Range("a" & Rows.Count).End(xlUp)(2)
            .AutoFilter
        Next
    End With
End Sub