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 ?

Solution 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

Solution 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.

Solution 3

Sub Makealist()

Application.ScreenUpdating = False

Dim rng As Range
' Destination of List
Worksheets("SomeWorksheet1").Activate
Worksheets("SomeWorksheet1").Range("SomeRange1").Select

' Range to Convert to list
Set rng = Worksheets("SomeWorksheet2").Range("SomeRange2")

' Makes sure that all "Blank cells are really Blank"
For Each c In rng.Cells
    If Len(c) = 0 Then
        c.Value = ""
    End If
Next

' Creates the list
For Each c In rng.Cells
    If IsEmpty(c.Value) = False Then
        Selection.Value = c.Value
        Selection.Offset(1, 0).Select
    End If
Next

Application.ScreenUpdating = True

End Sub

Solution 4

Assuming your range is A1:D4, here is a VBA macro that can do it (just putting the value down column E).

Sub RangeToColumn()

Dim varray As Variant
Dim i As Long, j As Long, k As Long

Application.ScreenUpdating = False
k = 1

varray = Range("A1:D4").value
For i = 1 To UBound(varray, 1)
    For j = 1 To UBound(varray, 2)
        Cells(k, 5).value = varray(i, j)
        k = k + 1
    Next
Next

Application.ScreenUpdating = True

End Sub

You could get fancy and use the dictionary object and transpose the array onto a column, but this is more simple, and the dictionary object does not work on Macs. You could also use "Range("E" & k)" instead of "Cells(k, 5)" but the Cells() is slightly faster since it does not require concatenation.

Please also note that by turning off the screen updating, this will run much faster.

View more solutions