Amend crosstab display

**BennyBoy01** · 02-08-2010, 10:25 AM

Trying to convert a crosstab type of display. Current data is set out like below:

Code Store1 Store2 Store3
a 2.3 2.5 2.6
b 2.2 2.5 2.9
c 1.8 2.4 3.1
d 2.0 2.2 2.1
e 2.1 2.3 2.7

In total I have 60+ rows for the Code column and 400+ store columns.

Eventually I would like the data to read like this:

Store Code Value
Store1 a 2.3
Store1 b 2.2
Store1 c 1.8
Store1 d 2.0
Store1 e 2.1
Store2 a 2.5
Store2 b 2.5
Store2 c 2.4

And so on.

Is there a way to write someting in excel to do this? Or a method through a pivot table?
Hope somebody can help.

Ben.

**ChemistB** · 02-08-2010, 11:23 AM

Here's how I did it.
To get the store names (dragged down)

Please Login or Register  to view this content.

where B1:D1 is the range where your store names are.

To get the codes (dragged down)

Please Login or Register  to view this content.

where A2:A6 is the range where your store names are.

To get the values (dragged down)

Please Login or Register  to view this content.

In my example, this is in C10 so B10 is the first code in your new table, A10 is the first store in your new table and B2:D6 is the range of your original data.
See attached. Does this work for you?