Shortest method required - choosing the correct combination

[ankitsingh2063]

Hi,

This may sound a bit silly, but I am trying to solve this:

If Employee or Manager comment sufficient for 50% or more of the instances (rows) in the below data, then output should put a "Yes" against that employee in a unique record.

Input data:

ID	Employee Comment Available	Employee Comment Sufficient	Manager Comment Available	Manager Comment Sufficient
1	Yes	Yes
1			Yes	Yes
1	Yes	Yes
1			Yes	Yes
1	Yes	Yes
1			Yes	Yes
1	Yes	Yes
1			Yes	Yes
1	Yes	Yes
1			Yes	Yes
2	Yes	Yes	Yes	Yes
2	Yes	Yes	Yes	Yes
2
2	Yes	Yes	Yes	Yes
2	Yes	Yes	Yes	Yes
3	Yes	Yes
3			Yes	Yes
4	Yes	Yes
4	Yes	Yes
4	Yes	Yes	Yes	Yes
4			Yes	Yes
5	Yes	Yes
5			Yes	Yes
5	Yes	Yes

Output data:

ID	Total Comments Sufficiency by Employee	Total Comments Sufficiency by Manager	Sufficient by Both
1	Yes
2	Yes	Yes	Yes
3	Yes	Yes	Yes
4	Yes
5	Yes

[danilang]

Hi @ankitsingh2063 

I'm a little confused by your results.  

ID =1  

Rows=10

Managers Comment and Sufficient = 5 (50%)

Total Total Comments Sufficiency by Manager = No

ID =3  

Rows=2

Managers Comment and Sufficient = 1 (50%)

Total Total Comments Sufficiency by Manager = Yes

Why are the results different in these cases?

Dan

[ankitsingh2063]

Sorry, my bad!

This is how the output should look like

ID	Total Comments Sufficiency by Employee	Total Comments Sufficiency by Manager	Sufficient by Both
1	Yes	Yes	Yes
2	Yes	Yes	Yes
3	Yes	Yes	Yes
4	Yes
5	Yes

[danilang · Accepted solution]

Here you go @ankitsingh2063 

The Cleanse Data tool removes all the leading and trailing spaces.  The Summarize tool groups by ID and calculates the total records for each ID and the Sufficiency records for Employees and Managers.  The formula tool does the math to determine if the comments reach the 50% mark and adds "Yes" in the "Both" column if both conditions are true

Results

Note that your input data didn't match your results for ID 4, 4 records and 2 manager comments, so I added an extra record with no manager comments

Dan

[ankitsingh2063]

Thank you so much @danilang