IS this possible?

[Janwillem]

Hi there. I have the following in Excel:


A B C D
1 hello 1
2 test 0
3 bye 1
4 hi 0
5 bybye 0
6 tjou 2


now on another place in this sheet i want to see all the words under each
other where the B value is bigger the 0 so:

E F G

1 hello
2 bye
3 tjou


So i only want to show th'ose words that have a bigger value. and i don't
want whitelines between the words like this ( =IF(B1>0,A1,"") )


E F G
1 hello
2
3 bye
4
5
6 tjou

[Max]

One non-array play ..

Use an empty col to the right, say col H

Put in H1: =IF(B1>0,ROW(),"")
Copy H1 down to say H100 to cover the max expected data in cols A and B

Put in E1:
=IF(ISERROR(SMALL(H:H,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(H:H,ROWS($A$1
:A1)),H:H,0)))

Copy E1 down to E100
(cover the same range as col H)

Col E will return the desired results from col A, all bunched neatly at the
top

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Janwillem" <jw@nietmailen.nl> wrote in message
news:42cb8f5d$0$2453$ba620dc5@text.nova.planet.nl...
> Hi there. I have the following in Excel:
>
>
> A B C D
> 1 hello 1
> 2 test 0
> 3 bye 1
> 4 hi 0
> 5 bybye 0
> 6 tjou 2
>
>
> now on another place in this sheet i want to see all the words under each
> other where the B value is bigger the 0 so:
>
> E F G
>
> 1 hello
> 2 bye
> 3 tjou
>
>
> So i only want to show th'ose words that have a bigger value. and i don't
> want whitelines between the words like this ( =IF(B1>0,A1,"") )
>
>
> E F G
> 1 hello
> 2
> 3 bye
> 4
> 5
> 6 tjou
>
>
>

[Bob Phillips]

How about using Data>Filter>Advanced Filter with a criteria of <> 0 and
unique records only.

--
HTH

Bob Phillips

"Janwillem" <jw@nietmailen.nl> wrote in message
news:42cb8f5d$0$2453$ba620dc5@text.nova.planet.nl...
> Hi there. I have the following in Excel:
>
>
> A B C D
> 1 hello 1
> 2 test 0
> 3 bye 1
> 4 hi 0
> 5 bybye 0
> 6 tjou 2
>
>
> now on another place in this sheet i want to see all the words under each
> other where the B value is bigger the 0 so:
>
> E F G
>
> 1 hello
> 2 bye
> 3 tjou
>
>
> So i only want to show th'ose words that have a bigger value. and i don't
> want whitelines between the words like this ( =IF(B1>0,A1,"") )
>
>
> E F G
> 1 hello
> 2
> 3 bye
> 4
> 5
> 6 tjou
>
>
>