These are the formulas for four columns, and also taking headers into account. I have limited the ranges to 9 numbers, but you have as many as you want (or even substitute the COUNTA formulas for their values). With the data on columns A to D the formulas could be:
E2: =OFFSET($A$2,INT((ROW()-2)/(COUNTA($B$2:$B$10)*(COUNTA($C$2:$C$10)*COUNTA($D$2:$D$10)))),0)
F2: =OFFSET($A$2,INT(MOD((ROW()-2),COUNTA($D$2:$D$10)*COUNTA($C$2:$C$10)
*COUNTA($B$2:$B$10))/(COUNTA($C$2:$C$10)*COUNTA($D$2:$D$10))),1)
G2: =OFFSET($A$2,INT(MOD((ROW()-2),COUNTA($D$2:$D$10)*COUNTA($C$2:$C$10))
/COUNTA($D$2:$D$10)),2)
H2: =OFFSET($A$2,MOD((ROW()-2),(COUNTA($D$2:$D$10))),3)
I am not sure if the pattern is clear. I have added the count of elements on the extra columns to the formulas, and change the offset and the ranges. For five columns it would follow the same pattern, with longer formulas.
No comments:
Post a Comment