Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IS this possible?
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(B10,A1,"") ) E F G 1 hello 2 3 bye 4 5 6 tjou |
#2
|
|||
|
|||
One non-array play ..
Use an empty col to the right, say col H Put in H1: =IF(B10,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 <atyahoo<dotcom ---- "Janwillem" wrote in message . .. 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(B10,A1,"") ) E F G 1 hello 2 3 bye 4 5 6 tjou |
#3
|
|||
|
|||
How about using DataFilterAdvanced Filter with a criteria of < 0 and
unique records only. -- HTH Bob Phillips "Janwillem" wrote in message . .. 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(B10,A1,"") ) E F G 1 hello 2 3 bye 4 5 6 tjou |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|