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: IDEmployee Comment AvailableEmployee Comment SufficientManager Comment AvailableManager Comment Sufficient1YesYes 1 YesYes1YesYes 1 YesYes1YesYes 1 YesYes1YesYes 1 YesYes1YesYes 1 YesYes2YesYesYesYes2YesYesYesYes2 2YesYesYesYes2YesYesYesYes3YesYes 3 YesYes4YesYes 4YesYes 4YesYesYesYes4 YesYes5YesYes 5 YesYes5YesYes Output data: IDTotal Comments Sufficiency by EmployeeTotal Comments Sufficiency by ManagerSufficient by Both1Yes 2YesYesYes3YesYesYes4Yes 5Yes

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

Parse

Accepted answers

danilang

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