Eliminating Blank cells

[Wayne.Miller]

I have a spread sheet of about 100 columns and 50 rows. Most of the
cells are blanks. The non-blanks contain numbers or text. There is at
least one non-blank cell in each row and each column. thus, each
column consists of a few header rows and then many blank cells and a
few scattered non-blank cells. I want to compact the columns by
removing all the blank cells so that the top rows of each column
contain only the non-blank cells.

If I select auto filter and sort a single column, I can select
non-blanks. However, it hides non-blank cells in the other columns.
If I use the Edit -> Go To and select blanks, it only selects rows of
data below my data set. It does not select the blanks within my data
set.

How can I move all the non-blank cells to the top of each column?

 

[macropod]

Hi Wayne,

Say you want to eliminate all blank cells in column A. Given that you've got
one or more heading rows, perhaps the easiest way is to insert a temporary
column B and put the following formula in B2:
=IF(A2="","",MAX(B$1:B1)+1)
Next, copy the formula down n rows to the last used row in column A, then
sort A2:Bn, with column B as the sort key. Finally, delete column B.

Repeat the process for each column you need to clear the empty cells from,
adjusting the formula's column references as you go.

Cheers

 

[Bob Greenblatt]

I have a spread sheet of about 100 columns and 50 rows. Most of the
cells are blanks. The non-blanks contain numbers or text. There is at
least one non-blank cell in each row and each column. thus, each
column consists of a few header rows and then many blank cells and a
few scattered non-blank cells. I want to compact the columns by
removing all the blank cells so that the top rows of each column
contain only the non-blank cells.

If I select auto filter and sort a single column, I can select
non-blanks. However, it hides non-blank cells in the other columns.
If I use the Edit -> Go To and select blanks, it only selects rows of
data below my data set. It does not select the blanks within my data
set.

How can I move all the non-blank cells to the top of each column?

One way to do this is:
-Select a column by clicking on the column header
-Edit menu-Goto-Special, click constants and unclick Logicals and errors
-click OK
-Edit menu-Copy
-Select a cell in row 1 in some unused column,
-click paste
-repeat for the other columns then recopy the new area to another sheet or
wherever you want.

 

[Harvey Waxman]

If I select auto filter and sort a single column, I can select
non-blanks. However, it hides non-blank cells in the other columns.
If I use the Edit -> Go To and select blanks, it only selects rows of
data below my data set. It does not select the blanks within my data
set.

How can I move all the non-blank cells to the top of each column?

A very inelegant approach would be to select one column and sort it in
descending order (I don't know what will happen to your header rows)

Select each column and cmd-y to repeat sort will be pretty quick.

You might have to copy the header rows to another sheet and delete them before
the sort. Then copy them back after the sort.

I said it was inelegant, right?
--
Harvey Waxman DMD
73 Wright Lane
Wickford, RI 02852
(remove thefrown to email)
http://righttax/blog

 

[Wayne.Miller]

Thank you all very much your help!!

I was hoping that someone would have an easy method to handle all the
columns at once, rather than one column at a time. Since the
spreadsheet has about 100 columns, doing one column at a time would
take some time and I have other spreadsheets like this one.

Does anyone, other than by one column at a time, have a way to compact
the columns by removing all the blank cells so that the top rows of
each column contain only the non-blank cells.

 

[Salmon Egg]

Thank you all very much your help!!

I was hoping that someone would have an easy method to handle all the
columns at once, rather than one column at a time. Since the
spreadsheet has about 100 columns, doing one column at a time would
take some time and I have other spreadsheets like this one.

Does anyone, other than by one column at a time, have a way to compact
the columns by removing all the blank cells so that the top rows of
each column contain only the non-blank cells.

It is not exactly clear to me what is being asked. You can always select the
cells in question and use Delete from the Edit menu. You can select entire
rows or entire columns and also delete, but many times that is not what may
be desired. Please be more specific.

Bill
-- Ferme le Bush