Moving data to a new cell

Posted by Joe B on May 24, 2001 1:53 AM

I have about 55,000 items of data where I have to trim the data to use only the first 6 characters. I used left() to do this. Then I had to use some code to insert a blank line after each row. I want to move my new data from column B to sit under it's predecessor in Column A. I can obviously record a macro to do this for one item but how do I do it for all of them?

Existing data looks like this:

Name333 Name33

Name343 Name34

Name353 Name35

I want it to look like this

Name333
Name33
Name343
Name34
Name353
Name35

I've thought about exporting a csv file into Access but otherwise I'm stuck.

thanks

Joe

Posted by Aladin Akyurek on May 24, 2001 2:35 AM

Joe

If this is not a repetitive operation, you can do the following.

I'll assume that your data occuppy cells in A from A1 on and in B from B1 on.

In C1 enter: =A1
In C2 enter: =IF(C1=A1,B1,A2) [ copy down this as far as needed ]

Then copy all of the cells in C, select the option Edit|Paste Special, and choose Values in order to paste these values in C. You can the delete columns A and B.

Hope this helps.

Aladin

{"Name333","Name33";"","";"Name343","Name34";"","";"Name353","Name35";"","";"Name363","Name36"}

Posted by Dave Hawley on May 24, 2001 2:40 AM

Joe, could you email me a copy and I'll write a macro for you.

Dave
OzGrid Business Applications

Posted by mseyf on May 24, 2001 6:15 AM

Highlight the area containing data in the first column.
From the menu bar select Edit>Goto>Special>Blanks.
All the blank cell should be highlighted.
On the Keyboard hit the '=' key, then hit the right arrow, then the up arrow, then hold down the 'Ctrl' key and hit 'Enter'.
By holding down the 'Ctrl' while you hit enter will cause the formual to be copied all the highlighted cells.
You can then Copy>Paste Special>Values the first column to change the formulas to values.

HTH

Mark

Posted by Eric on May 24, 2001 7:15 AM

Column offset method

Offset the first column (A) of "new" data by one row. So Column A data is every other row starting at 2, and column (B) data is every other row starting at row 1. Then in column C use the formula =if(a1>b1,a1,b1) and that should stack them like you want in (C), then paste as values.