Ignoring blanks from Column(s)

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hello

I have a column which is 40 rows deep.
In the column E2:E41 which has text and "".
In a another column H I would like to just display the text on
sequential rows.

Example
E8 = Hotel
E15= Taxi
E23 = Restaurant
E40= Car

I would like in display in column H
H2= Hotel
H3= Taxi
H4= Restaurant
H4= Car

Using formula only no VBA

Thanks

Michael
 
Try this:

=IF(ISERR(SMALL(IF(ISTEXT($E$2:$E$41),ROW(INDIRECT("1:"&ROWS($E$2:$E$41)))),ROWS($1:1))),"",INDEX($E$2:$E$41,SMALL(IF(ISTEXT($E$2:$E$41),ROW(INDIRECT("1:"&ROWS($E$2:$E$41)))),ROWS($1:1))))

CTRL-SHIFT-ENTER (not just ENTER)
Copy down as far as need
 
On Sat, 2 Dec 2006 08:31:00 -0800, Teethless mama
Teethless mama unfortunately this formula did not work.
I simply copied what was it E8 to H8 etc and left the blanks in place.
 
Back
Top