Combine Multiple Columns into One Column without Macro.

Combine Multiple Columns into One Column without Macro.

To Combine Multiple columns(A2:F21) into one column(K2:K121) without macro, it is convenient to use OFFSET function .

For Offset function ( K Column), we need Rows(I:I) and Column(J:J) number.

Steps:
1) Column H is for S.No.. Start H2 fom zero & for H3 formula is =H2+1 and drag down the formula. In our Example, we have 120 entries - sno is 0 to 119.

2) Column I is Adjusted Row, formula in I2 is =MOD(H2,20). Since each rows has 20 entries, that's why we used 20 in MOD function.

3) Column J is Adjusted column, formula in J2 is =INT(H2/20). 

4) Column K is Combined list, formula in K2 is =OFFSET($A$2,I2,J2).

Thank You !!