Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Manipulating a column array into different cell locations
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Manipulating a column array into different cell locations
Joe wrote...
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Manipulating a column array into different cell locations
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. "Harlan Grove" wrote: Joe wrote... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Manipulating a column array into different cell locations
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. Joe wrote: 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. "Harlan Grove" wrote: Joe wrote... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Manipulating a column array into different cell locations
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 Joe wrote: 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? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Manipulating a column array into different cell locations
Dave Peterson wrote...
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFill pattern based on cell locations | Excel Discussion (Misc queries) | |||
Changing cell locations with formula? | Excel Discussion (Misc queries) | |||
InputBox to select cell locations | Excel Worksheet Functions | |||
Manipulating Cell Contents? | Excel Discussion (Misc queries) | |||
Filling excel entire row/column instead of single cell from an array | Excel Worksheet Functions |