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 ?

what is the difference between output and raw data? You should be more specific about how the raw data look like and how you want it to look after applying the macro. — hoooman, Jan 22 '13 at 17:01
@Ankur - you should show what you have tried so far, so we don't duplicate work already done. Ideally, you'll just loop through each row, then move on to the next one. How do you know when you're at the end of a row, however? — LittleBobbyTables - Au Revoir, Jan 22 '13 at 17:22
http://stackoverflow.com/questions/14298522/convert-through-vba-row-with-columns-of-data-into-column-with-multiple-rows-in-e — Hiten004, Jan 22 '13 at 17:33

SeanC · Answer 1 · 2013-01-22T21:13:32.630

6

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

edited Jan 22 '13 at 21:13

answered Jan 22 '13 at 20:28

SeanC

15,695
5
45
66

I was about to answer similarly but canceled due to your answer: +1! Only change I would make: Use `INDEX` instead of `OFFSET` - it will deliver the same result but is non-volatile and therefore much faster after the first calculation run!!! – Peter Albert Jan 22 '13 at 21:06
1

@PeterAlbert, nice thought. I fixed my formula to be INDEX – SeanC Jan 22 '13 at 21:13
I don't see a mention that this is an array formula, and needs to be entered using the Ctrl+Shift+Enter method... am I wrong? – Code Jockey Feb 23 '16 at 15:50
@CodeJockey, this is not an array formula. It uses a calculation of the current row to work out where to get the data from. `INDEX` will allow you to indicate the position in an array, much like a vba array – SeanC Feb 23 '16 at 22:27

score 2 · Answer 2 · answered Dec 04 '15 at 19:25

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.

answered Dec 04 '15 at 19:25

Rob360

67
1
6

(ROW()-ROW($CELL$)) starts at 0, while ROW() at the first column starts at 1, so it should be (ROW()-ROW($CELL$) + 1) Edit: I now notice that he has -1 that you removed so my comment is invalid – Jonathan Camilleri Dec 15 '15 at 13:19
Very nice! I've replaced $Range$ and $Cell$ with range names, now have this as a nice utility formula with no customisation necessary – Phil Jul 10 '17 at 13:02

Gaijinhunter · Answer 3 · 2013-01-24T20:25:44.930

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.

score 1 · Answer 4 · answered Apr 16 '18 at 15:17

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

Convert Multiple Rows into Single Stacked Column in Excel

4 Answers4