Pull data to new tab if matching certain criteria

[gmazz]

Thank you for taking the time to help me with this issue. I have a grid that I have attached. There are a number of "Benefits" listed in column A on the tab labeled "Master". Than I show each of the US states starting in column P through column BN. Each state says "Yes" or "No" for each corresponding benefit. What I need to do is create a new tab (labeled "All Benefits") and use some type of formula that will show all the "Benefits" in column A and than only pull ONLY the states that have "Yes" listed for each benefit. So for example, in the attachment, the first state listed in the Master tab, is Column P and is the state of Alabama (AL). As you can see, they have "Yes" listed for every benefit in column A. Therefore I would want to pull the entire column of P in the second tab. However, column Q on the Master tab is for the state of Arkansas (AK) and they have some "No" entries, therefore that state would not be pulled into the second tab.

Something else to consider is: Ultimately I will be adding more tabs that will aim to pull the states that have "Yes" for a few of the benefits but not all of them. So for example, maybe the third tab I create will be for only the first five benefits from Column A (Missed Connection, Travel Delay, Baggage and Person Effects, Baggage Delay, and Non-Medical Emergency Evacuation). Therefore I would only want to pull the states that show "Yes" for these 5 benefits. So ideally I would like the formula to be able to be customized where I can adjust it to pull the states that match whatever benefits I am looking for.

If you have any questions or concerns please do not hesitate to post a reply. I really appreciate anyone providing some help with this.

Thank you.

-Garrett

[gmazz]

Not sure if there is a rule against bumping so hopefully I don't upset any admins doing this.

[FDibbins]

gmazz, the rule is to wait at least a day before bumping

[FDibbins]

Try this approach, using a helper row (I used row 1). You can hide that row, or set the text font to white on white so it looks invisible

Put this in P1 and copy across...
=IF(COUNTIF(P$4:P$141,"no")>0,O1,O1+1)

Then to pull in your data...
B3=IFERROR(INDEX(Master!$P$3:$BN$141,MATCH('All Benefits'!$A3,Master!$A$3:$A$141,0),MATCH(COLUMN(A$1),Master!$P$1:$BN$1,0)),"")
copied down and across
(Delete B5, it is not needed)

To change this for counts of "no" >0, just change that part of the helper row formula

[gmazz]

Sorry thanks for the heads up about bumping. Which tab does each formula go on? And does the helper row go in both tabs or just one?

[FDibbins]

sorry for not specifying...

On Master sheet...
Put this in P1 and copy across...
=IF(COUNTIF(P$4:P$141,"no")>0,O1,O1+1)

On all Benefits sheet...
B3=IFERROR(INDEX(Master!$P$3:$BN$141,MATCH('All Benefits'!$A3,Master!$A$3:$A$141,0),MATCH(COLUMN(A$1),Master!$P$1:$BN$1,0)),"")

[gmazz]

Ahhh perfect ok this worked after I tweaked it a bit. I forgot to mention that on the All Benefits tab, in some case the formula need to pick up the values in the A column and in some case the formula needs to pick up the values in the B column. I have revised the document and attached it for you to see what I am talking about. Notice how on the All Benefits tab, the formula in row 35 is different than in row 36. Not sure if their is one formula I could use that would account for whether the value is in column A or B. If you know of one, please feel free to mention it.

My next question is, I added a third tab that you can see. I am trying to make the formula work so that it pulls the data correctly. It seems to be working except that it's missing one state. I did a spot check on the master tab using a countif formula to see which states have "Yes" in every cell from row 48 through 110 and I showed 42 states. When I tried copying the formula over to the third tab, it only pulled in 41 states (TX was missing). Do you see anything in my formula that is wrong?

[gmazz]

bump......