Manipulating a column array into different cell locations

[Guest]

I have a long list of 400 names in a column array A1:A400

I want to "double space" this data.

That is, introduce a blank row after each name in the column in order to get
a new
array that now contains the same data spaced out in B1 B3 B5 ....etc....B800

Is there an easy way to do it without dragging and dropping 400 times?

 

[Harlan Grove]

I have a long list of 400 names in a column array A1:A400

I want to "double space" this data.

That is, introduce a blank row after each name in the column in
order to get a new array that now contains the same data spaced
out in B1 B3 B5 ....etc....B800

Is there an easy way to do it without dragging and dropping 400
times?

Select B1:B799 (no need to select B800).

Type =IF(MOD(ROW(),2),INDEX(A$1:A$400,INT((ROW()+1)/2)),"") and hold
down a [Ctrl] key before pressing the [Enter] key.

With B1:B799 still selected, press [F5] to bring up the Go To dialog,
click on the Special... button to bring up the Go To Special dialog,
select Formulas and uncheck all types except Text, click OK to select
all cells in B1:B799 that evaluate to "", then press [Delete] to clear
the contents of those cells.

 

[Guest]

Thank you Harlan it works very well.

The only part I did not understand was the last part.

After unchecking "all types except Text" in Go to Special dialog and
clicking OK it brought me back to the B1:B799 selection.

If I then press delete it clears all the arrray not just the "" parts.
So I just copied the array and did a Paste Special/values selected in
another location.

I have a long list of 400 names in a column array A1:A400

I want to "double space" this data.

That is, introduce a blank row after each name in the column in
order to get a new array that now contains the same data spaced
out in B1 B3 B5 ....etc....B800

Is there an easy way to do it without dragging and dropping 400
times?

Select B1:B799 (no need to select B800).

Type =IF(MOD(ROW(),2),INDEX(A$1:A$400,INT((ROW()+1)/2)),"") and hold
down a [Ctrl] key before pressing the [Enter] key.

With B1:B799 still selected, press [F5] to bring up the Go To dialog,
click on the Special... button to bring up the Go To Special dialog,
select Formulas and uncheck all types except Text, click OK to select
all cells in B1:B799 that evaluate to "", then press [Delete] to clear
the contents of those cells.

 

[Dave Peterson]

If you change Harlan's formula to:
=IF(MOD(ROW(),2),INDEX(A$1:A$400,INT((ROW()+1)/2)),NA())

You can use the Edit|Goto|special|formulas and leave Errors selected (and
unselect everything else).

And when you did your copy|paste special|values, you could use the same
location.

Thank you Harlan it works very well.

The only part I did not understand was the last part.

After unchecking "all types except Text" in Go to Special dialog and
clicking OK it brought me back to the B1:B799 selection.

If I then press delete it clears all the arrray not just the "" parts.
So I just copied the array and did a Paste Special/values selected in
another location.

I have a long list of 400 names in a column array A1:A400

I want to "double space" this data.

That is, introduce a blank row after each name in the column in
order to get a new array that now contains the same data spaced
out in B1 B3 B5 ....etc....B800

Is there an easy way to do it without dragging and dropping 400
times?

Select B1:B799 (no need to select B800).

Type =IF(MOD(ROW(),2),INDEX(A$1:A$400,INT((ROW()+1)/2)),"") and hold
down a [Ctrl] key before pressing the [Enter] key.

With B1:B799 still selected, press [F5] to bring up the Go To dialog,
click on the Special... button to bring up the Go To Special dialog,
select Formulas and uncheck all types except Text, click OK to select
all cells in B1:B799 that evaluate to "", then press [Delete] to clear
the contents of those cells.

 

[Dave Peterson]

Saved from a previous post:

I think that this is usually a bad idea. It can mess up filters, graphs,
pivottables, ...

If you want to make it look double spaced, then increase the rowheight.

But you can do it a few ways.

One way (manual):
Insert a new helper column
put =row() in the top cell of that new column and drag down (>400 rows)
Then convert those formulas to values (edit|copy, edit|paste special|values)

Then copy those numbers to the rows directly beneath the >400 rows.

Now select the whole range (row 1 to > row 800).

Sort your data by that column.
Delete the helper column.

Another way is to use a macro.

This picks out a column that has data in it on the last row -- I used column A.

Option Explicit
Sub testme()
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

With ActiveSheet
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'to increase the rowheight to twice the height
'.Rows(iRow).RowHeight = 2 * .Rows(iRow).RowHeight
'to insert a new row
.Rows(iRow).Insert
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Harlan Grove]

If you change Harlan's formula to:
=IF(MOD(ROW(),2),INDEX(A$1:A$400,INT((ROW()+1)/2)),NA())

You can use the Edit|Goto|special|formulas and leave Errors
selected (and unselect everything else).

....

I screwed that up.

There's another alternative. Copy A1:A400 into B1:B400, enter =ROW()
in C1, then double click on C1's fill handle to fill it into C2:C400.
Copy C1:C400 into C401:C800. With C401:C800 still selected and C401
the active cell, type

=C1+0.5

and press [Ctrl]+[Enter], select B1:C800 and run the menu command Data

Sort, sorting the range on column C in ascending order. Once sorted,

clear C1:C800.