move rows from column to column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have Column b with Name in one row and number in 2nd row want to move all
numbers to Column a and leave name in Column b without having to move each
one seperate. 1500 records.
 
Use a help column, in the first adjacent cell type

=ISNUMBER(A2)

where A2 is the first cell with name
copy down 1500 rows by either dragging the formula or
double click the lower right corner of the help cell (as long as it is in an
adjacent column)
Then apply data>filter>autofilter and filter on FALSE in the help columns,
select all visible cells and copy and paste somewhere else, then select TRUE
from the
dropdown and select all numbers and copy and paste next to the names you
previously copied

You can also apply autofilter without a help column, use custom and select
less than zzzzzzzzz
that will give you the names and then select is less than and type a number
greater than the greatest number in your list like 9999999999999, that will
give you all the numbers


--


Regards,


Peo Sjoblom
 
Lets say we have in column B:

Mike
1
John
23
David
15
Nigel
17
Trevor
19
George
31

and we want in columns A&B:

1 Mike
23 John
15 David
17 Nigel
19 Trevor
31 George

Then just run:

Sub movem()
n = Cells(Rows.Count, 2).End(xlUp).Row
k = 1
For i = 2 To n Step 2
Cells(k, 1).Value = Cells(i, 2).Value
k = k + 1
Next

For i = n To 2 Step -2
Cells(i, 2).Delete Shift:=xlUp
Next
End Sub
 
Back
Top