How to separate x y data in 1 column to 2 colums of x and y data?

[DaHou]

I have column of x,y data where A1 is an x value and A2 a y ,then A3 is an x
A4 a y etc. There are several hundred values. I'd like to be able to parse
out all the y values in the 1st column, place them in a second column, then
remove the blank cells in both columns so that the x and y data are aligned
in two separate columns.

Any tips will be greatly appreciated.
DaHou

 

[Dave Peterson]

Put this in B1:
=index(a:a,row()*2-1)

Put this in C1:
=index(a:a,row()*2)

Select B1:C1
Drag down as far as you need.

Select columns B:C
edit|copy
edit|Paste special|Values

delete column A
(if you're happy)

 

[DaHou]

Dave, Thanks for the help. I must be doing something wrong as B1 and C1 fill
in correctly however when I select both columns and copy then paste
special/values, the values for B1 and C1 are repeated for all cells. What am
I doing wrong?

Thanks
dave H.

 

[DaHou]

Dave- I found the bug, paste special/formulas does it.

Thanks
Dave H

 

[Dave Peterson]

This won't convert the formulas to values.

Are you sure you're copying the formulas from B1:C1 to B###:C### before you do
the next step?

Do you have any filtering turned on?

Do you have calculation set to automatic?

 

[DaHou]

Hi Dave,
I copy the formulas from B1:C1 to B###:C### , then paste special with
formulas selected. The values from A:A are automatically transferred to the
appropriate cells in B:C. -Everything looks fine.

I initially tried pasting values but the results in B1:C1 were copied into
the other cells. I'm not a seasoned excel user so perhaps I'm coasting on
dumb luck, but everything seems to work correctly.

Best
Dave H.

 

[Dave Peterson]

The order of the things you do is very important <bg>. I guess just like most
things in life.