>> List values in a range (with condition) row by row

[collegeitdept]

I was wondering if anyone can help me this problem I have with Excel.

I have a range of data (2 columns) with ID numbers in the first column and a value of "Y" (transfer student) in the corresponding column.


Column A Column B
1405 Y
1510 Y
2202
1761 Y
1821 Y
1954 Y
6255
etc...



I need a formula that will list all the ID numbers in column A if its corresponding value (in column B) is "Y"..... but skip ones that are not.

(if i use the IF formula, i would have to go row-by-row, and have 0 values in rows that are not equal to "Y".... and I would have to know the number of students are on the list (the list will change monthly).


IE: I would like this:

1405
1510
1761
1821
1954
etc...



Thanks.


BTW... Visual Basic is not permitted at my company, so I cannot use a macro.

 

[Domenic]

Assuming that A2:B8 contains the data, try...

C2:

=COUNTIF(B2:B8,"Y")

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D$2:D2)<=$C$2,INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8="Y",ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(D$2:D2))),"")

Hope this helps!

xl-central.com

 

[jasonb75]

Deleted, missed something in your post and solution was posted while I was typing

 

[Fazza]

Another way is using a pivot table - no formulas used. Set your column B to a page field and filter for the 'Y' entries.