Compare User's Current vs Previous Access

[fjlorenzo]

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

[Prometheus · Accepted 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.

 

 

 

 

 

[fjlorenzo]

Thank you @Prometheus .