Converting crosstab data to a list

  • Thread starter Igor, Annet, Linda & Ramon
  • Start date
I

Igor, Annet, Linda & Ramon

Hi,

I'm struggeling to find an efficient way of converting data that are stored
in a crosstable format to an excel list. Anyone any solutions?

Thanks in advance.
 
H

Harlan Grove

Debra Dalgleish said:
To normalize data that's in a crosstab format, you can use the
"unpivot' technique described by John Walkenbach:

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

OP didn't mention that the data within the crosstab was numeric. Pivot
tables do squat all with text.

The formula approach. If the entire crosstab range were in a range named
XTAB, use the following formulas.

A2:
=INDEX(XTAB,2+INT((ROW()-2)/(COLUMNS(XTAB)-1)),1)

B2:
=INDEX(XTAB,1,2+MOD(ROW()-2,COLUMNS(XTAB)-1))

C2:
=INDEX(XTAB,2+INT((ROW()-2)/(COLUMNS(XTAB)-1)),
2+MOD(ROW()-2,COLUMNS(XTAB)-1))

Select A2:C2 and fill down as far as needed. Columns A and C formulas will
evaluate to #REF! when XTAB has been exhausted.
 
Top