Convert Multiple Rows into Single Stacked Column in Excel

Question

I have this huge data of Market Share of various brands which looks like this:

1111 2222 3333 4444
5555      7777 8888
9999 0001 0002
0004 0005 0006 0007

What macro code can be used to get output of:

1111
2222
3333
4444
5555
<emptyCell>
7777
8888
9999
0001
0002
<emptyCell>
0004
0005
0006
0007

The Empty cells must also be considered.

Also is there a possibility for getting the output in other Sheet ?

Answer 1

Changed to INDEX for a less processor intensive version

in row 1 of whatever sheet you want to copy the data in to:

=INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)

copy this down, and once zeros start appearing, you are at the end. (This is the only issue - blank cells will become zero with this. if you wish to preserve the blanks too, then you need this:

=IF(ISBLANK(INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)),"",INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1))

)

if you are not starting at the first row, then change the ROW() to ROW()-X where X is the number of rows down from the top (i.e. 1 for row 2, 2 for row 3, 799 for row 800)
If there are a different number of columns, change the 4 to the appropriate number

Answer 2

Modified from SeanC's answer (thanks buddy) to turn into a generalized usage so that people with other range dimensions and starting cells can use it:

Replace '$RANGE$' with references to your range Replace '$CELL$' with reference to the first cell of the output column:

=INDEX( $RANGE$ ,INT((ROW()-ROW( $CELL$ ))/COLUMNS( $RANGE$ ))+1,MOD(ROW()-ROW( $CELL$ ),COLUMNS( $RANGE$ ))+1)

Drag this down. Of course, make sure both $RANGE$ and $CELL$ are fixed with '$' signs on both the row and column.