All values in column A should state yes if column B shows yes in column C?

[JoshuaElphee]

I have a large report which includes three main fields for this issue: Project (specific to a row), Program (may show numerous "projects" under the same Program #), and "Rule 21" (Y or N field).

I need to take this large workbook and ensure that each "Project" shows "Y" if *any* project with the same "Program" shows "Y" in the column "Rule 21".  I am unable to figure out how to get the transpose function to meet this need.

Project	Program	Rule 21
alpha 1	Uno	N
alpha 3	Uno	Y
Charlie 7	Dos	Y

Is the above example, the desired result is to add a new row stating "Rule 21 applies to Program" with the value stating "Y" for projects alpha 1 and alpha 3.

[Carolyn · Accepted solution]

This is what I would do -

1) I would add a formula field (number format) and have it put a 1 if Rule 21 = "Y" and 0 if Rule 21 = "N"

2) Then I would summarize by Program and Sum my new field

3) Join that back, based on Program

4) Add a new column to state that Rule 21 Applies to the Program any time the Sum > 1 with whatever wording you want

5) Select to clear off the excess fields

 

[binuacs · Accepted solution]

@JoshuaElphee i created a workflow based on my understanding

[Raj · Accepted solution]

@JoshuaElphee One other way to achieve this is using summarize tool 
1- group by project, concat the rule 21
2- if you concat field contains Y then logic is applied to group else it is not applied.