Need help to write formula in Excel to auto show only cells with values

Giganews Newsgroups
Subject: Need help to write formula in Excel to auto show only cells with values
Posted by:  Genesis (u2088…@gmail.com)
Date: Thu, 16 Aug 2007

Greetings all Excel experts!

I am using Excel 2003. Is it possible to write a formula to take a
column of data with blank cells in between in COLUMN A  and transfer
those data over to a different column (COLUMN B) without the blank
cells WITHOUT using pivot and/or macros.  The only way I can achieve
this today is to record a macro that would
1. Copy the entire data range in column A
2. Paste special --> paste value to column B
3. Sort column B descending.

This is the only way I know how but it is using a macro to do this.
Are there any ways to achieve this same result WITHOUT pivot and
macro? See data sample below:

Column A is what the data I'm getting. Notice that cells A4, A5 and A8
are blanks.  I need it to look like in Colunn B. The final results in
Column B does not have to be in that order but it CANNOT have blank
cells in between.

Any help would be greatly appreciated.

A                                              B
1  Forks                                    1  Forks
2  Knives                                    2  Knives
3  Spoons                                  3  Spoons
4                                              4  Chopsticks
5                                              5  Napkins
6 Chopsticks                              6  Cups
7 Napkins                                  7
8                                              8
9 Cups                                      9

Replies