Converting data from every second to a new column
 

Here's my problem:

I've got a dataset wherein every second row corresponds to the row
above it (i.e., I've only got one column).

I'd like to create two columns. Surely there must be a way to do this
using a macro or the OFFSET formula, but I haven't the faintest idea
of how to actually do it.

Help?

Converting data from every second to a new column
 

Okay, here's a mainly physical way of doing it. Assume your data
starts in A1, so A1:A2, A3:A4 etc are paired together. In B1 enter
this formula:

=IF(MOD(ROW(B1),2)=0,A1,"")

Then copy this down by double-clicking the fill handle with B1
selected (the small black square in the bottom right corner of the
cursor). You should just have data showing in the even-numbered rows
of column B. Fix the values in column B, by highlighting all the cells
with the formula in, then click <copy then Edit | Paste Special |
Values | OK then <Esc. Now, select cell B1 and click on Edit | Delete
and choose Shift Cells Up then OK - you now have the data from A2
showing in B1, A4 showing in B3 etc. Then highlight column B and click
on Data | Filter | Autofilter to turn it on, and from the filter pull-
down arrow choose Blanks (you may need to scroll down as it will be at
the bottom of the list). Then you should highlight all those rows that
are visible (with data in column A but no data in column B) and click
on Edit | Delete Row.

Finally, select All from the filter pull-down in B1 and then Data |
Filter | Autofilter again to turn it off. You should now have what you
asked for.

Hope this helps.

Pete



On Aug 20, 11:56*pm, PRC wrote:
Here's my problem:

I've got a dataset wherein every second row corresponds to the row
above it (i.e., I've only got one column).

I'd like to create two columns. *Surely there must be a way to do this
using a macro or the OFFSET formula, but I haven't the faintest idea
of how to actually do it.

Help?

Converting data from every second to a new column
 

Alright, here is a process which can do what you want that doesn't require
the use of VBA.

You'll need to create two columns. Say that the data starts in A1. So in
cell B1 type in the formula

=IF(NOT(MOD(ROW(A1),2)=0),A1)

Then in cell C1 put in the formula

=IF(MOD(ROW(A1),2)=0,A1)

Now copy and paste those formulas down to the end of your data in column A

Then highlight columns B and C.

Hit CTRL + G, Then click on Special,
Now click on Formulas and uncheck "Numbers" and "Text", then select OK

Now right click on one of the highlighted values and select "Delete". Then
select the option to delete upwards.

Then copy and paste the values and you should be good.


"PRC" wrote:

Here's my problem:

I've got a dataset wherein every second row corresponds to the row
above it (i.e., I've only got one column).

I'd like to create two columns. Surely there must be a way to do this
using a macro or the OFFSET formula, but I haven't the faintest idea
of how to actually do it.

Help?

Converting data from every second to a new column
 

Haha, sorry bout the semi duplicate post, I wrote it before I knew that you
responded. But that is a pretty clever way of doing it.

"Pete_UK" wrote:

Okay, here's a mainly physical way of doing it. Assume your data
starts in A1, so A1:A2, A3:A4 etc are paired together. In B1 enter
this formula:

=IF(MOD(ROW(B1),2)=0,A1,"")

Then copy this down by double-clicking the fill handle with B1
selected (the small black square in the bottom right corner of the
cursor). You should just have data showing in the even-numbered rows
of column B. Fix the values in column B, by highlighting all the cells
with the formula in, then click <copy then Edit | Paste Special |
Values | OK then <Esc. Now, select cell B1 and click on Edit | Delete
and choose Shift Cells Up then OK - you now have the data from A2
showing in B1, A4 showing in B3 etc. Then highlight column B and click
on Data | Filter | Autofilter to turn it on, and from the filter pull-
down arrow choose Blanks (you may need to scroll down as it will be at
the bottom of the list). Then you should highlight all those rows that
are visible (with data in column A but no data in column B) and click
on Edit | Delete Row.

Finally, select All from the filter pull-down in B1 and then Data |
Filter | Autofilter again to turn it off. You should now have what you
asked for.

Hope this helps.

Pete



On Aug 20, 11:56 pm, PRC wrote:
Here's my problem:

I've got a dataset wherein every second row corresponds to the row
above it (i.e., I've only got one column).

I'd like to create two columns. Surely there must be a way to do this
using a macro or the OFFSET formula, but I haven't the faintest idea
of how to actually do it.

Help?

Converting data from every second to a new column
 

Thanks - some posts take longer to show (and to type) than others.

Pete

On Aug 21, 12:31*am, akphidelt
wrote:
Haha, sorry bout the semi duplicate post, I wrote it before I knew that you
responded. But that is a pretty clever way of doing it.