Check out Chip Pearsons site:
http://www.cpearson.com/excel/noblanks.htm

You'll find the following array formula:

=IF(ROW()-ROW(noblanksrange)+1ROWS(blanksrange)-
COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL(
(IF(blanksrange<"",ROW(blanksrange),ROW()+ROWS(bl anksrange))),
ROW()-ROW(noblanksrange)+1),COLUMN(blanksrange),4)))

Define the following ranges:
labelsrange is the range on wks1 that contains the text label entries.
blanksrange is the range on wks 1 column B - the range of cells that
will have numeric values entered into them - some of the cells will
remain empty.
noblanksrange is the corresponding range on wks 2 col B - has to be
the same size range as blanksrange on wks1 and starting on the same
row.

Copy the above formula to the first cell of noblanksrange on wks2. Now
double click on the cell and enter it as an array using
ctrl-shift-enter. Now highlight noblanksrange and fill down. This
should give you the list of values without the empty rows.

In Wks 2 Col A copy the following formula - which is a variation on mr.
pearson's:

=IF(ROW()-ROW(noblanksrange)+1ROWS(blanksrange)-
COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL(
(IF(blanksrange<"",ROW(blanksrange),ROW()+ROWS(bl anksrange))),
ROW()-ROW(noblanksrange)+1),COLUMN(labelsrange),4)))

Enter it as an array as described above.

The only difference is instead of extracting the numeric values in col
b it extracts the labels in col A.

Seems to be working for me. Hope it helps.

Richard



tmented Wrote:
Hi I was wondering how I would go about performing this operation.
I have a list of Items set up in sheet 1, column A, all of them are
text.
in column b I would enter a quantity.
in sheet 2 I want collumn A to reflect the list from sheet 1 but only
if there is a value in collumn B.
this is easy enough to do using an if statement but I would like to fix
it so that if
row 1 doesn't have a value it looks at row 2 and so on The purpose is
to eliminate empty rows in sheet 2.
Can anyone help with this
Thanks
Lisa


--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=393645