How to condense multiple excel rows to get one row summary as per the logic explained

All3.png



I have a spreadsheet that has the security roles of each table a user has along with the permissions that security role has for the table. I have to condense them into a summary report as explained below. I'd appreciate if you can suggest how to achieve this.


A user can have access to a table via multiple security roles, some security roles might provide elevated access and others might provide lower access but the elevated access would take precedence.


#Image 1

In image 1, we can see the different security roles Peter Parker and Mary Jane has to Contact & Account tables, along with the permissions each security role offer.

I have to condense spreadsheet shown in Image 1 to Image 3. Following rules need to be governed.

1) There should be only one row per "Full Name & Table" combination.


2) All the "Roles" available for a "Full Name & Table" combination should be concatenated into a single cell.

3) The permissions associated with each security role for create, read, write.... should be condensed with the one having the highest permission. The permission hierarchy is as below.

Organization > PCBU > BU > User > None


Let us take the example of Peter Parker for Contact table. He has 5 security roles for Contact. For create the permissions provided by each roles are as follows

Account Manager - None

Sales Manager - User

Sales Operations - Organization

Marketing - User

Insurance - PCBU

So in the condensed report under create I should have Organization, because permission wise Organization > PCBU > BU > User > None


Thanks in advance

Try this,

Please Login or Register  to view this content.

Another solution for the whole table inclusive the headers in one go:

Please try

Please Login or Register  to view this content.

Yoy are Welcome!

Thanks for the nice question, feedback and rep. .

Glad to have helped.