Cleaning up data

Hello everyone,
I have a file which lists inventory by store. The problem is, the store numbers are listed first, then the inventory is listed underneath. Each item is seperated by a SKU (unique identifier). The width of each inventory/store listing is either 13 or 14 units wide.
I am trying to get all of this information in a format which is one line tall and about 200 lines wide.
For example:
SKU, Description, Size, Inventory Quantity Store 00, Inventory Quantity Store 01, Inventory Quantity Store 02, ...Inventory Quantity Store 500..., etc. Please keep in mind I have about 25,000 lines of informatin in my real files, so a macro approach is preferred.
Could you point me in the right direction? I attached a file for better understanding. Thank you!

Why do you want to do this? Unless you specifically are wanting to do this for data entry purposes for a legacy system, you would be better off putting your data in a table, and then use a pivot table to produce the output you describe.

Ie, Create a table with the following headings
Sku, description, size, location, quantity.

Then insert a pivot table.

Would that work?

Try

Please Login or Register  to view this content.

TestWithCode.xlsm

Jindon,
This works very well for the first 9 items, but stumbles when it gets to "Coke". The store numbers should correspond to the lines beginning with "Quantity". So, for the example "Coke", store 0 should correspond to 0, store 1 to 3, store 2 to 0, store 3 to 6, etc. I know it's strange. Also, it stops right after the coke example. If I had more lines, perhaps 25,000, how would I get this to work?
Thank you again! I appreciate your help!

Then you need to show me the exact result from the sample data.

as an option

Please Login or Register  to view this content.