Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List if
Dear,
I am looking for a formula to list out the names in a column only if it meets the set criteria. Column A Column B ColumnH Column I ABC 4 BCD EFG BCD 1.5 DEF FGH CDE 5 DEF 1 EFG 6 FGH 8 Column A has the names and column B has their scores. I want to list all the names whose score is less than 2 in column H and the list of names whose score is greater than or equal to 6 in column I. I could able to do that using a simple IF statement but it is leaving a blank value if it is more than 2. Like wise in column I it is leaving a blank cell if it less than 2. =IF(AND(B10,B1<2),B1,"") I wanted to get only the names without any blanks. Please suggest me on this. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List if
Source data as posted in A1:B1 down
To list all the names whose score is less than 2 in column D In C1: =IF(AND(B10,B1<2),ROW(),"") This is the criteria col In D1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Copy C1:D1 down to cover the max expected extent of source data. Hide/Minimize col C. Col D will return the required results, all neatly packed at the top Repeat the construct likewise to extract as desired for names whose score is greater than or equal to 6. The criteria col would simply be: =IF(B1=6,ROW(),"") Wave your success, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Sasikiran" wrote: I am looking for a formula to list out the names in a column only if it meets the set criteria. Column A Column B ColumnH Column I ABC 4 BCD EFG BCD 1.5 DEF FGH CDE 5 DEF 1 EFG 6 FGH 8 Column A has the names and column B has their scores. I want to list all the names whose score is less than 2 in column H and the list of names whose score is greater than or equal to 6 in column I. I could able to do that using a simple IF statement but it is leaving a blank value if it is more than 2. Like wise in column I it is leaving a blank cell if it less than 2. =IF(AND(B10,B1<2),B1,"") I wanted to get only the names without any blanks. Please suggest me on this. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
List if
Hi,
Try this array formula in H1: =INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),"") ,ROW())) array - you must press Shift+Ctrl+Enter to enter the formula. In I1: =INDEX(A$1:A$6,LARGE(IF(B$1:B$6=6,ROW(B$1:B$6),"" ),ROW())) Drag these formulas down as far as necessary. They will return #NUM! errors after the last item is found, to prevent that you can get fancier with: =IF(ROW()<=COUNTIF($B$1:$B$6,"<2"),INDEX(A$1:A$6,S MALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW())),"") and =IF(ROW()<=COUNTIF($B$1:$B$6,"=6"),INDEX(A$1:A$6, LARGE(IF(B$1:B$6=6,ROW(B$1:B$6),""),ROW())),"") both array entered. These can be copied down as far as you wish. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sasikiran" wrote: Dear, I am looking for a formula to list out the names in a column only if it meets the set criteria. Column A Column B ColumnH Column I ABC 4 BCD EFG BCD 1.5 DEF FGH CDE 5 DEF 1 EFG 6 FGH 8 Column A has the names and column B has their scores. I want to list all the names whose score is less than 2 in column H and the list of names whose score is greater than or equal to 6 in column I. I could able to do that using a simple IF statement but it is leaving a blank value if it is more than 2. Like wise in column I it is leaving a blank cell if it less than 2. =IF(AND(B10,B1<2),B1,"") I wanted to get only the names without any blanks. Please suggest me on this. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
List if
Hi Shane,
The fancier formula isn't working according to my need. I've used the ISERROR function to remove the #NUM! errors. Thanks so much. =(IF(ISERROR(INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW( B$1:B$6),""),ROW()))),"",INDEX(A$1:A$6,SMALL(IF(B$ 1:B$6<2,ROW(B$1:B$6),""),ROW())))) "Shane Devenshire" wrote: Hi, Try this array formula in H1: =INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),"") ,ROW())) array - you must press Shift+Ctrl+Enter to enter the formula. In I1: =INDEX(A$1:A$6,LARGE(IF(B$1:B$6=6,ROW(B$1:B$6),"" ),ROW())) Drag these formulas down as far as necessary. They will return #NUM! errors after the last item is found, to prevent that you can get fancier with: =IF(ROW()<=COUNTIF($B$1:$B$6,"<2"),INDEX(A$1:A$6,S MALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW())),"") and =IF(ROW()<=COUNTIF($B$1:$B$6,"=6"),INDEX(A$1:A$6, LARGE(IF(B$1:B$6=6,ROW(B$1:B$6),""),ROW())),"") both array entered. These can be copied down as far as you wish. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sasikiran" wrote: Dear, I am looking for a formula to list out the names in a column only if it meets the set criteria. Column A Column B ColumnH Column I ABC 4 BCD EFG BCD 1.5 DEF FGH CDE 5 DEF 1 EFG 6 FGH 8 Column A has the names and column B has their scores. I want to list all the names whose score is less than 2 in column H and the list of names whose score is greater than or equal to 6 in column I. I could able to do that using a simple IF statement but it is leaving a blank value if it is more than 2. Like wise in column I it is leaving a blank cell if it less than 2. =IF(AND(B10,B1<2),B1,"") I wanted to get only the names without any blanks. Please suggest me on this. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
create new list from list A, but with exclusions from a list B | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |