Converting crosstab data to a list

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.

Hi!

Can you describe precisely how your crosstab is organised?

Alf


---
Message posted from http://www.ExcelForum.com/

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


Igor, Annet, Linda & Ramon wrote:
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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"Debra Dalgleish" wrote...
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.