Automation of process to transpose & concatenate data from one spreadsheet to another

[barneys]

I am trying to automate a transposing and concatenating process. I can do this manually but it is taking me hours.  If any one can help with a formula/macro (or whatever is appropriate) I would be most grateful.

I need to get the info in the column A of spreadsheet 1 into the format of column B in spreadsheet 2. I have attached samples of each for your reference.

The common data on both spreadsheets is the SKU in column A spreadsheet 2 which can also be found in the column A in spreadsheet 1.

Hope that makes sense

[#2]

@barneys See if the attached solution works for you. It uses some of the new dynamic array functions (UNIQUE, TRANSPOSE and FILTER). I imported the CSV file first, then added two helper columns to create the SKU number and to determine where the next SKU starts. The third sheet brings it all together, and works as a one time solution. If you intend to do this on a regular basis, you may want to explore Power Query.

I hope you can use it. If not, kindly ignore this post.

[#3]

@Riny_van_Eekelen thank you for your solution. I am going to see if I can replicate now on some more data.