Split column A into 4 columns

[Alpacino]

Hi,
Does anyone know a macro that will divide a list in column A into 4 columns.
E.g I have a list in column A goes down to row 100 I want to have the list broken up so it goes into column A B C D

A
B
C
D
E
F
G
H
I
Splits
Into

A. D. G
B. E. H
C. F. I

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG05May16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A100")
[COLOR="Navy"]For[/COLOR] n = 1 To 4
    Cells(1, n + 1).Resize(25) = Rng(25 * n - 24).Resize(25).value
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mirabeau]

Sub reorganize()
Dim lr&, x&, j&, k&
lr = Cells(Rows.Count, 1).End(3).Row
x = Int(lr / 4) + 1
For j = 1 To lr Step x
k = k + 1
Cells(j, 1).Resize(x).Cut Cells(1, k)
Next j
End Sub

 

[MickG]

A more flexible alternative than my first try:-

[COLOR="Navy"]Sub[/COLOR] MG05May58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] vRows [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
num = Int(Rng.Count / 4)
    [COLOR="Navy"]For[/COLOR] n = 1 To 4
        vRows = Application.Transpose((Evaluate("row(" & Rng.Offset((num * n) - num).Resize(num).Address & ")")))
        Cells(1, n + 1).Resize(num) = Application.Transpose(Application.Index(Rng.Value, vRows, 1))
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jeffreybrown]

If you would like a formula this seems to do the trick.

=INDEX($A:$A,(ROW(A1)+((COLUMN(A1)-1)*3)))&"."

 

[Alpacino]

Thanks it works a treat.

 

[MikeWx]

If the total number of values in Column A isn't known or can change, this variation on Jeff's formula will determine which values to put in the first three columns and put the remaining values in the fourth column until another multiple of four is exceeded.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>A</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">I.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Q.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Y.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>B</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">J.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">R.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Z.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>C</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">C.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">K.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">S.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">AA.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>D</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">D.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">L.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">T.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">BB.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>E</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">E.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">M.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">U.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">CC.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>F</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">F.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">N.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">V.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">DD.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>G</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">G.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">O.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">W.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>H</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">H.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">P.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">X.</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>I</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>J</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>K</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>L</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>M</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>N</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>O</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>P</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Q</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>R</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>S</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>T</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>U</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>V</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>W</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>X</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>Y</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>Z</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD>AA</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD>BB</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD>CC</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD>DD</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(ROW(A1)<=ROUNDUP(COUNTA($A:$A)/4,0),IF(INDEX($A:$A,(ROW(A1)+((COLUMN(A1)-1)*ROUNDUP(COUNTA($A:$A)/4,0))))<>"",INDEX($A:$A,(ROW(A1)+((COLUMN(A1)-1)*ROUNDUP(COUNTA($A:$A)/4,0))))&".",""),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4