Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It does not matter in what order the new table is.

    A   B   C   D            A   B   C            A   B   C   
 1      H1  H2  H3        1  H1  R1  V1        1  H1  R1  V1
 2   R1 V1  V2  V3   =>   2  H1  R2  V4   or   2  H2  R1  V2
 3   R2 V4  V5  V6        3  H1  R3  V7        3  H3  R1  V3
 4   R3 V7  V8  V9        4  H2  R1  V2        4  H1  R2  V4
                          5  H2  R2  V5        5  H2  R2  V5
                          6  H2  R3  V8        6  H3  R2  V6
                          7  H3  R1  V3        7  H1  R3  V7
                          8  H3  R2  V6        8  H2  R3  V8
                          9  H3  R3  V9        9  H3  R3  V8

I've been playing around with the OFFSET function to create the whole table but I feel like a combination of TRANSPOSE and V/HLOOKUP is required.

Thanks

EDIT

I have managed to come up with the correct formulas. If the data is in Sheet1 like in my example above, the formulas go in Sheet2:

[A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

[B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"")

[C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

The formulas are limited to B1:Z1 for the headings and A2:A99 for the rows (these can be increased to their maximums if required). The COUNTA() formula returns the number of cells that actually have values, which limits the number of rows returned to headings*rows. Otherwise the formulas would could go on for infinity because of the MOD function.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

I like this question :)

try those functions. insert them in h0, r0, v0 and drag them down

   h   r   v
0 (a) (b) (c)
1 | | | | | |
2 \ / \ / \ /
3  v   v   v

where:
h = header label
r = row label
v = value label

(a) =OFFSET($A$1,INT(H2/99)+1,MOD(H2,99)+1,1,1)
(b) =OFFSET($A$1,INT(H2/99)+1,0,1,1)
(c) =OFFSET($A$1,0,MOD(H2,99)+1,1,)

where H2 is a column with values: 0,1,2,3...

share|improve this answer
    
OFFSET is definitely the right approach here. TRANSPOSE will switch rows for columns, and probably leave just as much work to be done. I would be tempted to build the functions to use an actual count of the number of rows and columns of data, rather than fixed values. –  AdamV Jun 1 '10 at 9:09
    
I'm having trouble applying your formulas. Can you adjust them to my example above with 3 headings and 3 rows. The data being in Sheet1 and the formulas in Sheet2. Thanks. –  user21549 Jun 1 '10 at 14:44
    
using your formulas helped me come up with a solution. Thanks –  user21549 Jun 1 '10 at 15:47
add comment

I saw a slightly more generalized version of the solution provided by @Dick Kusleika at StackOverflow a few weeks back: Convert row with columns of data into column with multiple rows in Excel 2007. It seems to not have references to individual rows (using ROW() instead) but still requires that the number of columns be hard-coded.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.