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!

 

[RobP]

Hi, welcome to the forum.

Could I ask, if you have just the list of part numbers on SheetA, and you have the Orders on sheetB - presumably you have also the Part number somewhere in SheetB (as it would relate to an order). Then also you have the names & addresses etc. on SheetB.

It strikes me that SheetB already contains all the data that you are looking to add to SheetC, correct ?

what will differ in your list on SheetC, from the list you have already on SheetB please ?

I ask, as it might be possible just to filter the data you have on sheetB to get what you want out of it ?

Rob

 

[NZ22N67P]

Hi, welcome to the forum.

Could I ask, if you have just the list of part numbers on SheetA, and you have the Orders on sheetB - presumably you have also the Part number somewhere in SheetB (as it would relate to an order). Then also you have the names & addresses etc. on SheetB.

It strikes me that SheetB already contains all the data that you are looking to add to SheetC, correct ?

what will differ in your list on SheetC, from the list you have already on SheetB please ?

I ask, as it might be possible just to filter the data you have on sheetB to get what you want out of it ?

Rob

Rob - Thanks for your response. Sheet B includes huge numbers of part numbers and other special item codes which do not appear on Sheet A. Sorting Sheet B by customer, let's say, would still yield a data set which is still too cumbersome to comb through manually.

 

[RobP]

Understood - sometimes its just quicker to re-arrange your columns to get the data you want on screen, then just filter the rows you want to see. Whether you filter sheetB, or whether you copy only the rows you want to see onto another sheet - the number of rows should in theory be identical....

So then you are stuck with writing VBA code to generate a nested loop - which means its going to search through 25,000 part numbers 1,400 times before it finishes. Thats quite a big loop.
You would need to use an array to do so, rather than addressing specific cells directly (as that would take days to run). an Array I'll bet will still take 20+ mins to process all the data.

Hows your VBA coding skills ?

cheers
Rob

 

[NZ22N67P]

Understood - sometimes its just quicker to re-arrange your columns to get the data you want on screen, then just filter the rows you want to see. Whether you filter sheetB, or whether you copy only the rows you want to see onto another sheet - the number of rows should in theory be identical....

So then you are stuck with writing VBA code to generate a nested loop - which means its going to search through 25,000 part numbers 1,400 times before it finishes. Thats quite a big loop.
You would need to use an array to do so, rather than addressing specific cells directly (as that would take days to run). an Array I'll bet will still take 20+ mins to process all the data.

Hows your VBA coding skills ?

cheers
Rob

Rob - I recorded/wrote/modified some VBA 20 years ago for an Excel macro or two, but I am not an expert by any means. I had some experience in college with coding, as I am an engineer, but CS was not my major. I am familiar with if/then for/next loops, and it was my first thought on how to do this task, but Excel has a lot of functions which I don't know about, and I thought I should ask if any of these functions could make this task much easier than I originally envisioned.

Regarding the loops, my main conceptual issue is the nested nature of loop. Do you have any sample code I could work from and modify?

Thx

 

[RobP]

Hi, so I set up three sheets, named as "A", "B" and "C" as you requested, that look like this :

Just part numbers stored in Col A on Sheet "A"

nested_loops.xlsm
	A	B
1	Part1
2	Part2
3	Part3
4	Part4
5	Part5
A

PO Number, Company name, address, Account, qty, Part number, Price on sheet B

nested_loops.xlsm
	A	B	C	D	E	F	G
1	PO1234	company A	address1	AC1004	2	Part1	1.8
2	PO1235	companyB	address1	AC1005	4	Part1	1.8
3	PO1236	company C	address1	AC1006	1	Part2	2.2
4	PO1237	company D	address1	AC1007	2	Part3	1.35
5	PO1238	company A	address1	AC1004	1	Part5	0.98
6	PO1239	company A	address1	AC1004	1	Part1	1.8
7	PO1240	company E	address1	AC1008	3	Part4	2.41
B

Then we have the VBA code as such :

Sub Copy_Loops()

Dim parts, orders As Variant 'set arrays for each sheet (essentially meaning copy the block of data into memory for faster operations)
Dim x, y, Alast, Blast, Clast As Long

Alast = Worksheets("A").Cells(Rows.Count, 1).End(xlUp).Row  'find the last row of data in ColA of sheet"A".
Blast = Worksheets("B").Cells(Rows.Count, 1).End(xlUp).Row
Clast = Worksheets("C").Cells(Rows.Count, 1).End(xlUp).Row

parts = Sheets("A").Range(Cells(1, 1), Cells(Alast, 1)).Value  '"parts" array stores all part numbers from sheet A
orders = Sheets("B").Range("A" & 1, "G" & Blast).Value 'Orders array stores all data from sheetB

'nested loops are just one loop within another.. so here we have the x loop to step through part numbers on SheetA, y loop inside it to check all orders on sheetB
For x = 1 To Alast 'first step through each part number row on Sheet"A", assume data starts on Row 1.. modify as required

    For y = 1 To Blast
    
        If parts(x, 1) = orders(y, 6) Then
        
            Sheets("C").Range("A" & Clast + 1).Value = parts(x, 1) 'store part number from parts array location Row x,Col 1 in Sheet C col A
            Sheets("C").Range("B" & Clast + 1).Value = orders(y, 1) 'store PO number in Col B
            Sheets("C").Range("C" & Clast + 1).Value = orders(y, 4)  'store A/C number in Col C
            Sheets("C").Range("D" & Clast + 1).Value = orders(y, 2) ' store Company name in Col D
            'etc ... etc..
            Clast = Clast + 1 'update last row number for end of data on sheet C, ready for next part number to start on next row
        
        End If

    Next y
Next x

End Sub

If you run the code, assuming Sheet C is blank, it will give you the following output - note the Part numbers are all grouped together.

nested_loops.xlsm
	A	B	C	D	E
1
2	Part1	PO1234	AC1004	company A
3	Part1	PO1235	AC1005	companyB
4	Part1	PO1239	AC1004	company A
5	Part2	PO1236	AC1006	company C
6	Part3	PO1237	AC1007	company D
7	Part4	PO1240	AC1008	company E
8	Part5	PO1238	AC1004	company A
C

Hope it helps get you started .... cheers
Rob

 

[NZ22N67P]

Rob - this is excellent info! I will work on adapting this to my application and let you know if I have any questions

 

[NZ22N67P]

OK, so I'm getting runtime errors - Runtime error 1004 "Application-defined or object-defined error on the highlighted line. For some reason, if it does not trip an error on this line, I get a Runtime error 9 at the If/Then statement.

I've recreated your example in a separate sheet, but when I try to change ANYTHING (column references mainly) to correspond with the data set I have, it seems errors are tripped and I don't know why. Any guesses as to why this is happening? I even tried limiting Blast to 50 lines in case 20,000 was causing an issue.

 

[RobP]

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

 

[Automatrix]

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.