filtering multiple rows

Hi all, this is my first post. Here's my question:

I have a large sheet that is related according to rows. The columns are unimportant. There is likely an easy solution to this, but all I need to do is filter out all the blanks - basically squeeze the data from right to left. All I can think of is perhaps transposing the entire sheet, then filtering each column for non-blanks, but that would be very time consuming. I'm somewhat familiar with the advanced filter, but I'm not sure how I could use it here.

Originally Posted by gratuitous1

Hi all, this is my first post. Here's my question:

I have a large sheet that is related according to rows. The columns are unimportant. There is likely an easy solution to this, but all I need to do is filter out all the blanks - basically squeeze the data from right to left. All I can think of is perhaps transposing the entire sheet, then filtering each column for non-blanks, but that would be very time consuming. I'm somewhat familiar with the advanced filter, but I'm not sure how I could use it here.

Are you saying that you have blank columns that you want to eliminate, shifting columns to the right of a blank column to the left?

If so then just use the sort, but sort by columns rather than rows. Sorting columns appears to be little used or even known functionality. To test for a blank column and mark it as such use something like:

Please Login or Register  to view this content.

If the column has no data you'll get a 99999 value, otherwise the number of the column. Convert all the formulae to values with a Copy...PasteSpecial Values, then select the whole range of data, choose Data Sort, but select the 'Left to Right' option, using the row where you've got your 99999 numbers as the key field.

HTH

Here's what I'm trying to say: If I have something like this below, where o's are blanks,

A B C D
x o x x
x x
x o x
x o o x

then I want to eliminate all the blanks and shift everything to the left, like this

A B C
x x x
x x
x x
x x

All I want to do is sort each row independently of the others. Thanks in advance for any help.

******** bump

Highlight the block of data.

Edit > Goto > Special and select Blanks, OK.

Right-click, Delete and select Shift Cells Left.

Thanks alot.

I had a feeling it would be something simple.