Auto Populate a list based off of certain criteria

**finance123** · 03-29-2012, 02:53 PM

I'm trying to populate a list of security identifiers based upon a certain criteria. On the "Status" tab, I want to populate column A with the value from column A on the OUTPUT tab if the formulas in column N, Q, T, W, or Z on the output tab result in "Y". But I'm trying to only include the CUSIP whose threshold formula equaled a Y. The worksheet usually has thousands of lines of data so I have to create a list of only the securities that need to be looked into. Any ideas?

**NBVC** · 03-29-2012, 03:24 PM

I am not sure if you mean that a "Y" should appear in an or in ALL of those columns.. and what you mean by only include CUSIP whose threshold formula equaled Y....

Here is what I would do, nonetheless.

Use a helper column in the OUTPUT tab, in AA28, something like:

=IF(AND(N28="Y",Q28="Y",T28="Y",W28="Y",Z28="Y"),COUNT(AA$27:AA27)+1,"")

this check that all fields have a "Y"... can be altered to OR to check if any have a "Y".

Copy down formula.

Then in the other sheet enter formula:

=IF(AND(N28="Y",Q28="Y",T28="Y",W28="Y",Z28="Y"),COUNT(AA$27:AA27)+1,"")

copied down to get all matches...

**finance123** · 03-29-2012, 03:46 PM

I guess I'm first trying to populate column A on the status tab with the cusip from the output tab whose formula in column N of that tab resulted in Y, if not then the cusip would not show up on the status tab. I think a macro would probably work best for this but I am unsure of how to write it. After column B on the status tab populates then I would be able to run a vlookup for the results in the other columns for that particular cusip, not sure if that helps.

**NBVC** · 03-29-2012, 03:50 PM

All you would need to do is adjust the helper formula to:

=IF(N28="Y",COUNT(AA$27:AA27)+1,"")

copied down

The formula in the Status tab remains the same... just copy it down as far as you want.