automate delete blanks in data range

[mikesmith200550]

I have rows of answers to 10 “Multiple choice” questions (each questio
has 5 possible choices. Each row contains a 10 total answers, tha
could appear in any of the 50 cells that appear in columns. Therefor
there are random blank cells throughout the spreadsheet, but if I wer
to were to delete all the blanks cells and simply shift the cells t
the left, I achieve the desired effect (b/c there are is no one wh
could skip a question and I know the order of the questions). Which
can do by hand, but how do I automate the process of removing blan
cells from a data range

 

[Roger Govier]

Hi Mike

Rather than trying to delete the blank cells, you could place all the
results in a single column to the right of your data
With Question number in A, results in B:F enter the following formula in
column G
If the answers are Text
=LOOKUP("ZZZZ",B1:F1)
If the answers are numeric
=MAX(B1:F1) or =LOOKUP(99999,B1:F1)
Copy down through G2:G10
Now, hide columns B:F

I am assuming there can only be one answer per row, and 4 "Z's" will be
sufficiently different from any potential answer to achieve your
requirement.
--
Regards

Roger Govier


"mikesmith200550"
message
news:[email protected]...