Using Text to Columns combined with Transpose in a macro

Hi experts!

I'm struggling with the following problem. I guess it has something to do with a Transpose function, but can't figure it out..

Attached sample file has 2 sheets. The sheet "Original data" shows the format / layout in which the data is supplied, the sheet "expected result" is what I need to have as a final result.
The serial numbers (column I) are all typed into 1 cell, separated by spacing. I need all these serial numbers listed below eachother, each in a separate cell, as you can see in the tab "expected result", with their respective case numbers in a separate column.

I know it has something to do with 'text to columns' and the transpose array formula, but I can't put it together in a macro, as obviously the number of cases and quantity listed in the sheet "original data" will be different with each file and the macro has to take that into account.

Is there somekind of macro which can do the above?

Hopefully you can help me with this...

Many thanks in advance!!!

Peter

try

Please Login or Register  to view this content.

Perfect!!! Domo Arigato Dave and Thanks Jindon!
Star added!

Hi Dave / Jindon,

I thought it was solved... unfortunately I was given an additional challenge... In the first example file, the serial numbers were separated with 5 spaces. I can see this 'hardcoded' in the VBA code from Dave.
Now there seems to be instances whereby the serial numbers are separated by just 1 or 2 spaces, instead of 5. So is it possible to make this somehow adjustable in the VBA code as well? So that it will split the serial numbers if they are separated by any number of spaces in 1 cell?

Additionally, the first column in the sheet contains merged cells... they need to be unmerged and the casenumbers copied down to fill the empty cells.

Will it be possible at all to combine this into 1 VBA code? Or will this 2nd example file need a separate VBA code? In that case I can just make 2 "original data" sheets as in the first file and link each VBA macro to a sheet, correct?

Again, million thanks in advance for the help!!!

Regards,
Peter

Thanks Dave, almost perfect
Only thing is the Case No. (first column in the results). These need to be the numbers as listed in column A of the original data.
In the 2nd example file that I uploaded, you have cell A7 as merged cell (value is 1). That value 1 need to be listed for all the serial numbers in column I (total of 160 numbers).
The next value (cell A23, value 2) goes for the next 240 serial numbers etc...

The current result numbers each batch of 10 serial numbers with a sequential case number, so the last 10 numbers have case number 1000.
The result should be that the first 160 numbers have case number 1, then each following 240 numbers have case number 2, 3, 4 etc up to 42 as there are 42 cases in this example file.

Can you please review and let me know if this can be adjusted?

Thanks a million!!

This should work

Please Login or Register  to view this content.

Perfect, thanks Dave!!
I've no idea what the code exactly does, but it's working great!!

Regards,
Peter