1

I have data in an array/grid like this:

Name | Group | A1 | A2 | A3
name1 | Group1 | 14.95 | 19.95 | 0.00
name2 | Group2 | 4.95 | 9.95 | 12.95
name3 | Group2 | 49.95 | 0.00 | 0.00

And I need a way (without VBA if possible) to turn the data in to 4 columns

name1 | Group1 | 14.95 | A1
name1 | Group1 | 19.95 | A2
name1 | Group1 | 0.00 | A3
name2 | Group2 | 4.95 | A1
name2 | Group2 | 9.95 | A2
name2 | Group2 | 12.95 | A3
name3 | Group2 | 49.95 | A1
name3 | Group2 | 0.00 | A2
name3 | Group2 | 0.00 | A3

I've tried paste-transpose and pivot tables, but everything I know requires lots of manual deletion to get the items to line up.

Any help will be greatly appreciated.

0

would use the Power Query Add-In for this. It does not require any code or complex functions. Starting from scratch it will probably take less than 5 minutes to complete this task.

You can start a Query from an existing Excel Table. I would then use the Unpivot command to transform the data as you require.

http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx

The beauty of their implementation of Unpivot is that it will accept any further columns added (e.g. A4 , A5) and process them without any change in your Query definition.

I would rename columns as required and deliver the result to an Excel Table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.