MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Consolidating non blank cells in rows

Posted by giohess on March 27, 2001 7:37 PM

I have a spreadsheet with 20 questions by row in column A and the names of 15 people at the top of columns B thru P. The answers to the questions by each person appears in their named column in the same row as the question. Not everyone answered all the questions so I have blank cells throughout the columns. I plan on removing the names on the columns and consolidating the answers to each question without having any blank cells in between. Other than cut and paste every cell that has an answer, is there a way to move or consolidate the non-blank cells into the blank cells? Did I make any sense?

Posted by Dave Hawley on March 27, 2001 7:56 PM

Hi giohess

Let's see if I have got you right.

Instead of removing blank cells, why not fill them all in with some text e.g. No Answer

Select your entire table of answers
Push F5 then click "Special".
Select "Blanks" and click OK.
Now type: "No Answer" (without quotes)in the formula bar.
Push Ctl+Enter.

This will place the words "No Answer" in all your blank cells.


OzGrid Business Applications

Posted by bj on March 27, 2001 9:01 PM

Select the whole range of answers including blanks.
Select Edit/GoTo/Special/Blanks/OK
Select Edit/Delete/ShiftCellsLeft/OK

Posted by Dave Hawley on March 27, 2001 9:57 PM

That wont work!

I just tried that and it left blanks in the last 7 columns!

Nice attempt though :o)

Posted by bj on March 28, 2001 12:28 AM

Yes it does

Well yes, but it makes the answers in each row (i.e. each question's answers) contiguous, which is what the original posting requested.

Posted by giohess on March 28, 2001 6:01 PM

Thank you bj and Dave. I was able to finish the project on time.