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.
Solved! Go to 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
@JoshuaElphee i created a workflow based on my understanding
@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.