Function/Macro to return info from an order list based on a part number list

[NZ22N67P]

Hello - I have a list of part numbers (1400) on worksheet "A", and a list of orders (25K) on worksheet "B".

I'd like to take each item number appearing in a list on worksheet "A" and find instances of that part in worksheet "B", while returning data from adjacent cells (such as acct name & order number). The output of this can be put in worksheet "C". There will definitely be multiple instances of any given part number within worksheet "B".

What's the easiest way to do this?

Thanks!

 

[NZ22N67P]

Power Query: Make a Connection to worksheet B. Then make a query based on worksheet A, and merge with query B using a Left Outer join (all from A, matching from B). No VBA required in this case.

I've never heard of a PowerQuery - I'll look into the feature. From what I can see after a quick search online I can definitely use this in a lot of data analysis situations!

 

[NZ22N67P]

ok, so you confirmed that my example worked as it was when you recreated it - thats good.

Now all I need to know is what you have changed ?

Using F8 to step into the code (rather than F5 to run it all) by each line will allow you to hover over the variables and check if they are taking what you think they should be taking at each line in the code. Maybe a sample of your data on each column might be useful to see if you can share part of your sheet (using the XL2BB link above and doing a simple cut and paste once you've installed it).
Rgds
Rob

If I do the simplest thing possible and just run the macro as-is with NO modifications, Alast, Blast and Clast show the correct values, but "parts" throws a runtime error '1004' and shows as empty. FYI - the proper part number values are actually located in column B of sheet A, but just to be safe, I moved the pertinent data to column A, and came up with the same result.

 

[RobP]

I am assuming that your workbook tabnames have been changed to "A", "B", and "C" as I have them also, right ?

If you wanted to check for the last data in Col B, then you would amend it as such:

Alast = Worksheets("A").Cells(Rows.Count, 2).End(xlUp).Row

The Rows.Count,1 that was previous refers to Col 1, which is "A" in this case. By changing it to a 2, it looks at Col B .. etc.

but then you need to amend this line as such also :

parts = Sheets("A").Range(Cells(1, 2), Cells(Alast, 2)).Value

The Cells(1,1) means start in Row1,Col1 = "A1" so by changing it to Cells(1,2) = Row1,Col2 or "B1". Equally it ends in Cells(Alast,2) (or "B1460" if thats how many rows of data you have..)

if you worksheet tabs are labelled as mine are, and your data is in the same place as mine was .. and youre running the same code as I posted originally... I can't see why it fails..

posting your actual snippets of sheets (rather than a screen grab)will give us more info potentially..

Rgds
Rob

 

[NZ22N67P]

If I do the simplest thing possible and just run the macro as-is with NO modifications, Alast, Blast and Clast show the correct values, but "parts" throws a runtime error '1004' and shows as empty. FYI - the proper part number values are actually located in column B of sheet A, but just to be safe, I moved the pertinent data to column A, and came up with the same result.

One more thing - add-ins from external sources are not permitted per our IT policy, so I cannot post minisheets in the forum. Screenshots will have to do.

 

[NZ22N67P]

I am assuming that your workbook tabnames have been changed to "A", "B", and "C" as I have them also, right ?

If you wanted to check for the last data in Col B, then you would amend it as such:

Alast = Worksheets("A").Cells(Rows.Count, 2).End(xlUp).Row

The Rows.Count,1 that was previous refers to Col 1, which is "A" in this case. By changing it to a 2, it looks at Col B .. etc.

but then you need to amend this line as such also :

parts = Sheets("A").Range(Cells(1, 2), Cells(Alast, 2)).Value

The Cells(1,1) means start in Row1,Col1 = "A1" so by changing it to Cells(1,2) = Row1,Col2 or "B1". Equally it ends in Cells(Alast,2) (or "B1460" if thats how many rows of data you have..)

if you worksheet tabs are labelled as mine are, and your data is in the same place as mine was .. and youre running the same code as I posted originally... I can't see why it fails..

posting your actual snippets of sheets (rather than a screen grab)will give us more info potentially..

Rgds
Rob

Rob - The tab names have been changed to "A", "B", "C"... correct.

I know about the column addressing method - After having the errors, I just thought I should keep the macro totally as-is just to test that IT IS indeed returning values, even if they were from the wrong column.

 

[RobP]

No problem, - I wasn't sure how much you'd remembered since you last played with macros..

 

[Automatrix]

I've never heard of a PowerQuery - I'll look into the feature. From what I can see after a quick search online I can definitely use this in a lot of data analysis situations!

It's like a well-kept secret, and there's no good reason for it. It's easier to learn than formulas (and much easier than VBA), and more intuitive for non-programmers.