Return multiple rows of data based off export & insert line

Hi all, I cannot come up with a formula to do what I need and I'm still very new to VBA - so I'm not sure how to achieve this..

Basically I have an export which has a bunch of info I need summarised. Attached is an example. We have 6 different Production Lines that each of these BOM's (Bill of Materials) are for, and a list of all work order numbers (172****) associated with that line. For this export I have just got lines 1, 2 & 3 (I'm sure I can modify the code for the other 3 lines if I have a starting point).

The tab 'BOMData' has all of the data from the export. For each work order number will be a list of materials that go with that order (can range from a couple materials through to 7+ materials)

The tab 'BOMsAuto' is how I need it to be set up. Basically for each work order number, it would list all of the ingredients, then put a blank line in where the next work order is different, so forth and so on.

On the 'BOMsAuto' tab is a list of Work Orders (column G for Line 1) which is the order in which I need the BOM's to appear so that it's accurate. I tried using VLOOKUP to start with, but it won't go to the next line once completed and won't adjust when there are more materials on a particular order - it also doesn't incorporate an extra line between work orders. Is there a way to do this automatically, or to tie in with a macro?

Thank you so much for your time and help. It's Monday and my brain has stopped working completely.

Hi
This isn't a VBA solution, but it is a start.
I have attached a sheet that will retrieve the Work Order BOMdata data as you want.
However there is a problem;
if I am correct in my guess that the length of returned materials list is not constant, then I do not know a way to have the formula return all results for the first Order Number and then leave a blank line and repeat the process for the second Order number.

Hope this is some help :-)

Regards

Thanks Bobsone, that certainly gives me something to work with. You are absolutely correct that the amount of lines to be returned are not constant, and thus does not provide a 'line' to separate the materials, but it's certainly more automatic than it was before. Thanks once again, Bobsone. +rep for you.

If anyone else has any solutions, feel free to share

Cheers.

Hi
Thanks for the feedback, hopefully a complete solution can be found.
How do you generate the PO values in G2:G7, O2:O6 etc?

Regards

Hey Bobsone.

The PO values are generated from our scheduleof work orders. I just copy and paste them to that location, then use them to generate my report out of SAP/reference which order the results should return with.

The export then pastes in and the results come back. I am continuing to work with the solution you've posted, and am hoping I can create a macro afterwards which will neaten up the results.