From Several Columns to a Single Column with a Formula
This formula is useful in case you have a data table in the following format and you want to transform it to “Preferred Results” format.
A-Data-1 | B-Data-1 | C-Data-1 | D-Data-1 |
A-Data-2 | B-Data-2 | C-Data-2 | D-Data-2 |
A-Data-3 | B-Data-3 | C-Data-3 | D-Data-3 |
A-Data-4 | B-Data-4 | C-Data-4 | D-Data-4 |
Preferred Results:
A-Data-1 |
A-Data-2 |
A-Data-3 |
A-Data-4 |
B-Data-1 |
B-Data-2 |
B-Data-3 |
B-Data-4 |
C-Data-1 |
C-Data-2 |
C-Data-3 |
C-Data-4 |
D-Data-1 |
D-Data-2 |
D-Data-3 |
D-Data-4 |
Excel Formula:
$A$1:$D$4 – change to your data range
A$7:A7 – change to the first cell where your results data begins. In our example that would be the cell holding A-Data-1 data.
Apply the formula to the first data results cell and fill down.
=INDEX($A$1:$D$4,MOD(ROWS(A$7:A7)+ROWS($A$1:$D$4)-1,ROWS($A$1:$D$4))+1,INT((ROW()-ROW($A$7))/ROWS($A$1:$D$4))+1)