Create detail from crosstab info
Posted by Eric Nolan on September 21, 2001 10:11 AM
I need to derive detail rows of information (for importing into a database) from a crosstab of information.
I have:
2 4 6 8 10
2 .1 .1 .2 .2 .3
4 .2 .3 .4 .4 .5
6 .4 .4 .5 .6 .6
8 .5 .6 .6 .7 .7
10 .7 .8 .9 1 1
I need a new sheet (via macro?):
2 2 .1
2 4 .1
2 6 .2
2 8 .2
2 10 .3
4 2 .2
4 4 .3
4 6 .4
4 etc.
Help!
Posted by Mark W. on September 21, 2001 11:28 AM
Assuming that your sample data resides in cells
A1:F6...
1. Paste (n-1) copies of A2:A6 directly beneath
cell A6 where n is the number of data columns (5
in this case).
2. Sort (in ascending order) all rows below 1:1
by the values in column A (cells A2:A26).
3. Insert 2 new columns before column B.
4. Enter the array formula, {=TRANSPOSE($D$1:$H$1)}
into cells B2:B6 and fill down by double-clicking
on the selection fill handle (the black square
in the bottom, right-hand corner of the selection).
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
5. Enter the array formula, {=TRANSPOSE(D2:H2)},
into cells C2:C6 and fill down by double-clicking
on the selection fill handle.
6. Select column A:C and perform a Copy/Paste Special
Values, and then Delete or Clear all cells other
than those in columns A:C.
Your results should be...
2,2,0.1
2,4,0.1
2,6,0.2
2,8,0.2
2,10,0.3
4,2,0.2
4,4,0.3
4,6,0.4
4,8,0.4
4,10,0.5
6,2,0.4
6,4,0.4
6,6,0.5
6,8,0.6
6,10,0.6
8,2,0.5
8,4,0.6
8,6,0.6
8,8,0.7
8,10,0.7
10,2,0.7
10,4,0.8
10,6,0.9
10,8,1
10,10,1
