Converting columns into rows

[Guest]

I have a mass amount of data that needs to be converted from columns to rows.
The columns are Date, Customer Name, Product 1, Product 2, Product 3, etc...
- in the product column is the status of each product for that time period.
I need to convert the Product columns into rows of data so that the columns
become Date, Customer Name, Product, Status. I know that there is a
transpose function but I have A LOT of data to transpose and using this
function I have to manually transpose for each month and customer for up to
15 products which is very time consuming. I am wondering if there is a way
to automate this process. Also, for each division of the number of product
columns varies so whatever automation is recommended needs to be able to
adjust for the number of columns in a sheet.

Thanks!

 

[Debra Dalgleish]

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Before you 'unpivot' the data, concatenate the data in the first two
columns (Date/Customer). For example:

1. Insert a blank column C, and enter a heading in row 1, e.g. "Info"
2. In row 2, enter the following formula, and copy down to all rows:
=A2& "^"&B2
3. Copy the formula to the end of the data
4. On a blank worksheet, select cell A1

Then, follow John Walkenbach's instructions to unpivot the data, using
column C and the columns to the right.

To split the concatenated column,
1. Move the Info column to the right end of the data
2. Select the Info column
3. Choose Data>Text to Columns
4. Choose Delimited, click Next
5. In the 'Other' box, type: ^
6. Click Finish
7. Return the info columns to the far left of the data

 

[Guest]

Your instructions were perfect!! Thank you so much!!!