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:
Unit | Data | Player1 Role | Player1 | Monitor Role | Monitor | Substitute Role | Substitute |
A | Name1 | Owner | Claire | Preparer | Michael | Owner | Claire |
B | Name2 | Preparer | Michael | Owner | Claire | Manager | Sean |
C | Name1 | Checker | Ben | Checker | Ben | Manager | Sean |
D | Name4 | Manager | Sean | Checker | Ben | Owner | Claire |
Output:
Unit | Data | Player1 Role | Player1 | Monitor Role | Monitor | Substitute Role | Substitute |
A | Name1 | Owner | Claire | Preparer | Michael | Owner | Claire |
C | Name1 | Checker | Ben | Checker | Ben | Owner | Claire |
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.
Solved! Go to Solution.
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
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
Thank you for the help. I just had to tweak it a bit to make it work as per my requirement. 🙂