Splitting a variable length column into 4 colums

[locolindo]

Hi there,

I need to separate a column of data that varies in its length, into 4 equal columns that would change accordingly.

Let me give an example:


If the data row in column A = 60 rows --> I would get 4 columns (B, C, D, E), each with 15 values. (B1:B15=A1:A15; C1:C15=A16:A30; D1:D15=A31:A45; E1:E15=A46:A60)

If the data row in column A = 48 rows --> I would get 4 columns (B, C, D, E), each with 12 values. (B1:B12=A1:A12; C1:C12=A13:A24; D1:D12=A25:A36; E1:E12=A37:A48)

If the data row in column A = 20 rows --> I would get 4 columns (B, C, D, E), each with 5 values. (B1:B5=A1:A5; C1:C5=A6:A10; D1:D5=A11:A15; E1:E5=A16:A20)

and so on...

I tried to do it using array formulas, but could not get it to do it if the initial column has a different value. If I changed the initial number of rows, it would either fill the columns with zeroes, or not display all values.

There was a somewhat similar issue discussed here: http://www.mrexcel.com/forum/excel-...long-column-into-multiple-smaller-columns.htm , but in that case the initial column always had the same number of rows, and mine can change greatly (sometimes from from 20-1400 rows...).

I'd appreciate both formulas and macros, but formulas would work better, as I am not familiar with VBA...

Thanks!

Best,
Locolindo

 

[ghrain22]

Try:
=IF(ROW()<(COUNTA($A:$A)/4)*(COLUMN()-1),INDEX($A:$A,(COUNTA($A:$A)/4)*(COLUMN()-2)+ROW(),1),"")

In B1 and drag the formula appropriately

 

[locolindo]

Hi Ghrain,
Thanks for your help! The formula you created still has to be tweaked somewhat, but I think I can take it from here. It is a big help, though!
Thanks a lot!
Locolindo