Loop for change then transpose paste new sheet

Hi lovely clever people.

Once again my training for loops has been cancelled and I must ask you for help.

I have a spreadsheet (attached). What I need to do is create named ranges for each commodity type based on the assembly number. Not all assemblies have the same commodity types, some have loads and some just have the parent. So making it one big list would be pointless.

The only thing i can think of that wouldn't take 2 weeks to do manually is create a loop to run down the assembly field and at each change just transpose the data into a new sheet. (this is the bit i need help with)

Then i can just set the named ranges from the top field. I have another spreadsheet which will then use this data via indrect against the parent assembly number. We are making a lookup tool for engineers. So they will select manufacturer (not shown) then assembly and then a list of commodity types available.

I hope that makes sense.

Thank you in advance :o)

Hi lian.cragg
You forgot to attach the workbook

Can supply a after sheet in the workbook with a few line so I can see what you are trying to achieve.