Geek Answers Handbook

Convert multiple rows to one stack column in Excel

I have this huge data on the market share of various brands that look like this:

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

Which macro code can be used for output:

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

You should also consider empty cells.

Also there is an opportunity to get a conclusion in another sheet?

+4
source share
3 answers

Changed to INDEX for less processor version

in line 1 of any sheet on which you want to copy data:

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

copy this, and as soon as the zeros begin to appear, you are at the end. (This is the only problem - empty cells will become null with this. If you also want to keep spaces, you need this:

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

)

if you do not start from the first line, then change ROW() to ROW()-X , where X is the number of lines down (i.e. 1 for line 2, 2 for line 3, 799 for line 800)
If the number of columns is different, change the value of 4 to the appropriate number

+6
source

Changed from SeanC answer (thanks to a friend) to turn into generalized use so that people with different range sizes and starting cells can use it:

Replace '$ RANGE $' with a reference to your range Replace '$ CELL $' with a 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 it. Of course, make sure that both $ RANGE $ and $ CELL $ are fixed with $$ characters for both the row and column.

+1
source

Assuming your range is A1: D4, here is a VBA macro that can do this (just put the value in 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 can get fancy and use a dictionary object and transfer the array to a column, but this is simpler, and the dictionary object does not work on Mac computers. You can also use "Range (" E "and k)" instead of "Cells (k, 5)", but Cells () is slightly faster since it does not require concatenation.

Also note that by disabling screen refresh, this will work much faster.

0
source

More articles:


All Articles