Finding duplicates with conditions

Question

Hi All. I have one more request I need help with. I need to find the list of different assignees for same type of data across different unit. The input and desired output is as follows:

Input:

UnitDataPlayer1 RolePlayer1Monitor RoleMonitorSubstitute RoleSubstituteAName1OwnerClairePreparerMichaelOwnerClaireBName2PreparerMichaelOwnerClaireManagerSeanCName1CheckerBenCheckerBenManagerSeanDName4ManagerSeanCheckerBenOwnerClaire

Output:

UnitDataPlayer1 RolePlayer1Monitor RoleMonitorSubstitute RoleSubstituteAName1OwnerClairePreparerMichaelOwnerClaireCName1CheckerBenCheckerBenOwnerClaire

In the above example for the same data (name1) the assignees across different unit are different, which should have been the same. So, I need to find it as an output. Please help.

atcodedog05 · Accepted Answer

Hi @SCK

Adding to the previous solution.

Here is how you can do it.

1.Transpose so that all names comes in a single column.

2. Use summarize to find the count of occurrence for each name

3. Keep only the ones which occur more than once

4. Using summarize tool to remove duplicate occurrence of Unit and Data (removing duplicates is important to prevent one to many joins)

5. Join it back with the main data on Data and Unit

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

BrandonB · Accepted Answer

You can use a summarize tool to group by the Data column and also count the data column. Then follow the summarize tool with a filter tool where the count > 1. Then you can join this back to the data before the summarize on the Data column. Only the records where there is more than one row per name will return in the inner join.

Edit: @atcodedog05 great minds think alike!

Another approach is the “only unique” as explained here: http://www.chaosreignswithin.com/2014/08/only-unique-macro.html?m=1