Excel 2007 : .I have data for 1 observation in 2 columns that needs to be in 1 column

**spotte** · 10-11-2010, 12:11 PM

Hi,

I am trying to clean up some data before importing it into SPSS. I will attach a sample data to the post to show how the data looks.

The labels are in column A. The data are in columns B and C. I want to move the data in column C underneath the data in column B.

I have a number of "paired" columns like this in the spreadsheet. So, column B and C are paired then column D and E are paired, etc. all the way to CP and CQ.

Any ideas on how I can do this without cutting and pasting them all? Thanks in advance for any help on this.

**spotte** · 10-13-2010, 09:57 AM

bumping to the top....anyone have any advice on this question? Thanks in advance for any help you can provide.

**spotte** · 10-13-2010, 11:31 AM

Okay, I have been playing around with trying to get this data moved around and I was able to make a very simple macro to move the data the way I want, but I don't know the code to make it automate.

The macro is

Please Login or Register  to view this content.

What I would like to be able to do is have this macro run and check all the row 1 columns from C to CR. If the cell is blank then I would like it to execute the macro above, but of course the columns indicated in the macro above would need to change to match whatever column the code has identified as being a blank cell in row 1.

Anyone have any help for this? I am pretty mych a tyro when it comes to macros and programming in excel. Thanks in advance for any help.

**JBeaucaire** · 10-13-2010, 01:33 PM

Like so:

Please Login or Register  to view this content.

**spotte** · 10-13-2010, 01:56 PM

@JBeaucaire

Thank you very much for your reply!

I ran the code you posted and it works great, but for some reason stops processing after column BD in row 1 when there are still more cells in row 1 that are blank and need to be moved. Any ideas why it might be doing that?

Thanks!

Nevermind..I figured it out...for some reason there were spaces in the ones after that. This code works beautifully. Again, really appreciate your help on this JBeaucaire!