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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 07:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com