Home 
Search 
Today's Posts 
#1




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




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 doubleclicking 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 evennumbered 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 pulldown 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




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




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 doubleclicking 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 evennumbered 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 pulldown 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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Critical  Converting Single Column Address Data into Multiple Col  Excel Worksheet Functions  
Excell functions for converting column data  Excel Worksheet Functions  
Converting data in column to Phone Format  Excel Discussion (Misc queries)  
Converting a matrix of data into a single column  Excel Discussion (Misc queries)  
Converting an array of data into a single column  Excel Discussion (Misc queries) 