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
....

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