How do I separate one column into multiple columns?

[smoore21]

Hello,

I have two columns of data in an excel workbook that I need to convert into 5 separate columns. I need a Weeks column (ex: FW 2024-01), UPC column (ex: 1201), SKU column (ex: 12404), Product Description Column (ex: Coffee Cakes $1.30), and Cases Column (ex: 1,400). 

A few notes that may be useful:

1. The weeks are from FW 2024-01 to FW 2024-52. The Weeks are not spaced out evenly, there is a different number of product data in between each week.

2. The product descriptions are formatted differently. Some have price info like $1.30 and some do not.

3. This is fake data. There are 17K rows in the real data source.

[binuacs]

@smoore21 Can you provide the expected output based on the above input file? 

[smoore21]

Here you go:

[binuacs]

@smoore21 One way of doing this, this workflow was create based on the input file and the output file, let me know the workflow not giving you the expected result when you run with your actual data.

[alisonpitt]

I did this a slightly different way, which assumes each product is on two rows: one with the UPC and one with the description and # cases:

- identify week numbers in a new column, filter out week number rows

- identify UPCs and descriptions using multi-row formula tools

- grab unique records and parse SKUs from descriptions, tidy up

- separately, sum up cases for each UPC

- join them back together

Spoiler

Based on the number of repeated tools I used, I'm sure there's a more efficient way to do this, but hopefully it helps!

[KimLamNg]

I tried to make the workflow dynamic so it's a bit long.

Spoiler