Yes!!
So I have a working Sample Workbook for you.
Now we just need to figure out the simplest way for you to implement it.
Try this:
A) I'm assuming your worksheets have headers, so you need to adjust the formulas if there aren't any. If your worksheets have names other then the standard Sheet1, Sheet2, Sheet3 change it now to Sheet1, Sheet2, Sheet3 so you don't need to update the formulas. You can change the worksheet names after and it will auto update the formulas.
B) Create a Sheet2. In Sheet2 field A2 paste the following, then drag it down as to as many lines as Sheet1 has.
=IF(Sheet1!B2>0,Sheet1!A2,"")
this is pulling the data lines from Sheet1 that have quantity on order greater then 0You can ignore this sheet now. I'm just using it for the logic so your other worksheets look clean.
C) Create a Sheet3. Paste the following into Sheet3 field A2. Then click into the text and do CTRL+SHIFT+ENTER to make it an Array Formula.
Then, drag it down to as many lines as is in Sheet1. (The formula is counting up to 5001 lines, so change the number to higher if needed).
=(IFERROR(INDEX(Sheet2!A$2:A$5001,SMALL(IF(Sheet2!A$2:A$5001<>"",ROW(Sheet2!A$2:A$5001)-ROW(Sheet2!A$2)+1),ROWS(Sheet3!A$2:A2))),""))
E) In Sheet3 field B2, paste the following.
=IFERROR(INDEX(Sheet1!$B$2:$B$5001,MATCH($A2,Sheet1!$A$2:$A$5001)),"")
Let me know if there are any problems or changes you want to see.