Amend crosstab display

**BennyBoy01** · 02-08-2010, 09: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, 10:23 AM

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

=INDEX($B$1:$D$1,1,INT((ROW(A1)-1)/5)+1)

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

To get the codes (dragged down)

=INDEX($A$2:$A$6,MOD(ROW(A1)-1,5)+1)

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

To get the values (dragged down)

=INDEX($B$2:$D$6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$D$1,0))

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?