Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 20th 08, 11:56 PM posted to microsoft.public.excel.misc
PRC PRC is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 1
Default 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?

  #2   Report Post  
Old August 21st 08, 12:23 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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?


  #3   Report Post  
Old August 21st 08, 12:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 461
Default 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?

  #4   Report Post  
Old August 21st 08, 12:31 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 461
Default 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?



  #5   Report Post  
Old August 21st 08, 01:04 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Critical - Converting Single Column Address Data into Multiple Col A.J. Excel Worksheet Functions 2 July 10th 08 06:39 PM
Excell functions for converting column data MegM Excel Worksheet Functions 7 April 15th 08 08:05 PM
Converting data in column to Phone Format Prasad Gopinath Excel Discussion (Misc queries) 1 February 15th 08 11:13 PM
Converting a matrix of data into a single column Hosley Excel Discussion (Misc queries) 6 April 19th 07 06:07 AM
Converting an array of data into a single column Raj Excel Discussion (Misc queries) 0 August 15th 06 09:21 PM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017