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

Question

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.

ProjectProgramRule 21alpha 1

UnoNalpha 3UnoYCharlie 7DosY

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.

ccanterman · Accepted Answer

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

binu_acs · Accepted Answer

@JoshuaElphee i created a workflow based on my understanding

Raj · Accepted Answer

@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.