Reformat data to remove blanks

**yunesm** · 04-21-2011, 10:41 AM

Hello,
I have a spreadsheet (attached) that I need to clean-up.

The spreadsheet contains the free text responses from a 14 question survey. If a respondent did not leave a comment there is a blank. For each question this leaves an inconsistent spacing between the respondents.

I have no need to be able to tie back a comment to the specific respondent so to facilitate categorizing and analyzing the data I would like to reformat the data so each question is continuous list of actual responses.

I know how to find empty cells or rows and delete them; however, that doesn't help me here.

The spreadsheet I've attached shows what the data currently looks like (RAW) and what I'm trying to do (FINAL).

Any help is very much appreciated.
Thanks!

**TMS** · 04-21-2011, 10:50 AM

You could use a Helper Column, for example, column O

In cell O2, put the formula:

=COUNTIF(A2:N2,"")

and drag down to the end of the data

Now use Autofilter and filter on the value 14 (14 blank cells)

Delete the rows highlighted

Turn off the Autofilter

Delete the Helper Column

A bit tidier but not exactly what you asked for ;-)

Regards

**yunesm** · 04-21-2011, 10:58 AM

Thanks for the comment TMShucks! No, you're right this is a bit different than what I'm looking for. It's easy to remove the rows that are all blank with VB but what I need is to have all continous cells. Once I delete the all blank rows I'm still left with many blank cells.

I could do this manually with the amount of data I have now BUT we are surveying ~ 175 more people. I need to clean-up and analyze the final data set and I do not want to have to do that manually.

Thanks!

**TMS** · 04-21-2011, 11:08 AM

One easy way would be to select each column and sort each one individually.

Record a macro as you do it ... or at least the first one ... and you have the basis for your code.

Then delete the rows where the cells are all blanks (as described earlier)

Regards