Ignoring blanks from Column(s)
 

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

Ignoring blanks from Column(s)
 

Maybe.........Data Filter Autofilter "non-blanks"

Vaya con Dios,
Chuck, CABGx3


"Michael" wrote in message
...
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

Ignoring blanks from Column(s)
 

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


"Michael" wrote:

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

Ignoring blanks from Column(s)
 

On Sat, 2 Dec 2006 08:31:00 -0800, Teethless mama
wrote:
Teethless mama unfortunately this formula did not work.
I simply copied what was it E8 to H8 etc and left the blanks in place.


Try this:

=IF(ISERR(SMALL(IF(ISTEXT($E$2:$E$41),ROW(INDIREC T("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


"Michael" wrote:

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