r/excel•
Time4chilli

Need help spacing very large column

unsolved

I have a large colomn of values(approx 3000). I need to insert 11 clear cells in between each value to match a timeline in another column. How can I add 11 cells in between eatch value without having to do it manually?

Posted Mar 20, 2019, 8:16 PM UTC

76
FerdySpuffy2•

Maybe try creating a new column, set the first to =A1 or whatever the top value is, skip 11 lines, =A2, then copy the series down

Once that's done copy and paste special>values then delete the original column

1
Time4chilliOP•

Will try it out. Thank you very much

1
Time4chilliOP•

Still having some issues. Excel wants to add 10's to the = ; so if i out in =a1 all the way through =a8 then try and copy down the column, the next formula is =a18, and then =a28

1
plantsci•

Hi,

just did a quick google and found this:

https://stackoverflow.com/questions/28282473/how-to-convert-a-matrix-to-a-single-column-using-excel

which is quite similar...with a bit of modification it should work...

This is how to turn a matrix into a single column.

​

first step: turn your column into a matrix:

put your column of 3000 starting at cell A1, then fill 11 cells across with a random symbol that is not in your data anywhere eg @ (it needs to not be in your data as it will be deleted later and you don't want to delete any of your data) B1:L1 and then auto fill down to the length of the first column.

​

Then in column M1 type:

=OFFSET($A$1,TRUNC((ROW()-1)/12),MOD(ROW()-1,12))

I don't fully understand this function, but the 12 refers to how many columns it looks at.

drag this formula down as far as the column should be long. Sorry I don't know a pretty way for this, but it would be 3000*12 = 36000. Damn that's a long way!! {optional extra, You could jump to 3601 and place an "x" in cell N3600 then in cell N1 place an "x" and Ctrl+C this cell and move to cell N2 with the down arrow key. Then hold Ctrl+Shirt+downarrowkey this will highlight all the cells N2:N3600 then Ctrl+V to fill then all with "x". You can then highlight cell M1 and double tap the square box in the bottom right and the formula should auto complete as far is it has a filled cell next to it, in this case to M3600.}

This will give all your data into a single column.

Now copy and paste special the values of this column (M1)

Highlight this column, then use Ctrl+F to find or replace, select the replace tab and input @ (or whichever symbol you used) in the "find what" box and leave the "replace with" box empty and press "replace all".

​

You should now have a single column with your data and 11 blank spaces between each part.

​

It's not a pretty solution but it should work.

​

​

​

1
plantsci•

Hi,

I asked this problem to my colleague and he had a much simpler solution, with no formula needed:

Your data is in cells A1:A3000

Add a header to your data (it can be anything, eg "head1"), so now your data is A2:A3001 [you might already have a header]

In B1 add a header, eg "head2"

In B2 make 1 - 12 (going down, ending in B13)

Copy and paste 1-12, 3000 times. Each time pick up the set before as well so it gets exponentially quicker (2,4,8,16,...)

Highlight both rows and in the 'data' tab at the top select 'filter'

In "head2" click on the drop down arrow and select "Sort smallest to largest"

Column A now has your data with 11 spaces between.

Delete column B [and delete column A header, if required]

Hey presto!

​

​

1
Time4chilliOP•

Thanks everyone for your help! Solved. Your answers were very helpful

1