Create rows from columns

[JamesF]

I have a customer code and 4 columns that contain product orders

I need to create a csv with a line per customer code for each one of the product codes
eg, if Customer 1234 wants 3 of each of the 4 colours, the table needs to create 4 rows for that customer, one for each product.

I cant work out how to generate rows from columns

[Brian_Dawson]

Hey James,

There’s ways to get this done, it just depends on exactly how you want the resulting table to look.

Option A

Results looks like this:

To get something like that, here’s what you’d do:

To break that down:

1. Take your separate columns of color quantities and create a new column that concatenates them altogether. (This will let us split into rows in future step)

2. Remove the color columns that were in your source data

3. Split your new column into rows, on commas, so that each color:quantity pair gets its own row (but keeps the account number its associated with)

4. Then, split the color:quantity pair into columns, on the colon character, so that quantity is in its own column.

5. Then rename your columns for clarity.

Option B

The easier method, which might well do what you need, is to flip the table.

That will get you a result like this:

This is all you’d need to do:

Hope that helps!

---

Brian Dawson
Automation Consultant

[JamesF]

Thanks Brian
Option A worked fantastically and opened my eyes to a couple of other use cases as well.
Appreciate your help