Hi Everyone,
I need to compare the access of all users from the current vs previous user listing. User can have one or more access and user's access is separated by a bar "|". Below are my tables. Thank you for your help.
Previous User List
Name | Access |
Alex | Client Read Only Prod Account|Client Production Modify Broker |
Allen | Client Read Only Prod Broker - Account |
Romy | Client Read Only Prod Broker - Account |
Malou | Client Read Only Prod Broker - Account |
Jane | Client UAT Test|Client Read Only Prod Broker - Account|Client Production Modify|Client UAT |
Ann | Client Read Only Prod Broker|Client Production Modify Account|Client Development |
Adrian | Client Production Modify Broker|Client Production Modify|Client UAT|Client Full Access|Client Development|Client Production |
Current User List
Name | Access |
Alex | Client Production Modify Broker|Client Read Only Prod Account |
Allen | Client Read Only Prod Broker - Account |
Romy | Client Read Only Prod Broker - Account |
Malou | Client Production Modify Broker |
Jane | Client UAT Test|Client Production Modify|Client UAT|Client Read Only Prod Broker - Account |
Ann | Client Development|Client Production Modify Account|Client Read Only Prod Broker |
Adrian | Client Production Modify Broker|Client Production Modify|Client Development|Client UAT|Client Full Access|Client Production |
Solved! Go to Solution.
The first thing I did was parse out the access that each user has/had by using the Text to Columns tool and configured them both to "Split to rows." Then I unioned the data sets. At this point, you can see there are duplicates in the access column, so I used the Summarize tool to group by both Name and Access to remove duplicates for each user. After that, I used another Summarize tool to group by Name and concatenate Access with a "|" to separate the different values for each user.
Thank you @Prometheus .