Power Query Problems

How to Create Unique Segregation-of-Duties Role Pairs in Excel

Bushra Parveen Oct 22, 2026 868 views

Question details

The user needs to generate every unique combination of roles for a segregation-of-duties table without pairing a role with itself or creating reverse duplicates.

How to Create Unique Segregation-of-Duties Role Pairs in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating an access control or compliance matrix where all possible combinations of user roles must be listed dynamically to check for conflicts.
Observed behavior: Manual pairing or standard formulas create reverse duplicates (e.g., A-B and B-A) or require constant manual adjustments when the number of roles changes.

Before you start

Ensure your list of roles is formatted as an official Excel Table (press Ctrl+T). This ensures Power Query can automatically pick up newly added roles when refreshed.

Solution 1Recommended

Use Power Query to Generate Unique Role Pairs

Power Query is the most reliable and maintainable option. It creates a dynamic process that generates unique pairs and automatically expands when your source list changes.

This method involves loading your roles, creating a duplicate query, and using Index columns to filter out both identical pairs and reverse duplicates mathematically.

Load the roles into Power Query

Select your Excel Table containing the roles. Go to the Data tab on the ribbon and click 'From Table/Range'. This will open the Power Query Editor.

Add an Index Column

In the Power Query Editor, go to the Add Column tab and click 'Index Column'. Choose 'From 1' to assign a sequential number to each role.

Duplicate the query

In the Queries pane on the left, right-click your newly created query and select 'Duplicate'. You now have two identical lists, both with indexes.

Merge to create combinations

Go back to the first query. Instead of a standard merge, go to Add Column > Custom Column. Name it 'CrossJoin' and simply type the name of your duplicated query into the formula box. Click OK, then click the expand icon on the new column header to reveal all role combinations.

Filter out identical and reverse duplicates

To remove role self-pairings (A-A) and reverse duplicates (B-A), click the filter dropdown on the original Index column, go to Number Filters > Less Than, and select the duplicated Index column from the dropdown. Close and Load the query to your worksheet.

Use Power Query to Generate Unique Role Pairs

Dynamic Updates: Whenever you add or remove roles from your original Excel Table, simply right-click the Power Query output table and select 'Refresh' to generate the updated combinations.

Solution 2

Use Excel Formulas (IF and INDEX/MATCH)

A basic formula-based approach that can identify combinations, best suited for a small, static list of roles.

Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet Tasks

If you are managing complex data sets and need a reliable, highly compatible alternative to Microsoft Office, WPS Office is an excellent choice. It handles standard Excel formats seamlessly while remaining completely free and lightweight.

1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install and Launch: Follow the on-screen instructions to install the software, then launch WPS Spreadsheet.
3. Open your Excel files: Open your existing .xlsx workbooks directly in WPS Spreadsheet and continue analyzing your role pairs with full formatting compatibility.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Free and lightweight, ensuring smooth operation even on older devices.Familiar user interface requires no learning curve to transition.Robust built-in data handling and advanced functions for compliance tracking.

microsoft office alternative - wps office

Frequently Asked Questions

What are segregation-of-duties role pairs?

Segregation-of-duties (SoD) role pairs are combinations of user permissions or roles used in compliance matrices. Organizations analyze these pairs to identify conflicts of interest, ensuring that no single individual has enough system access to both execute and conceal a fraudulent transaction.

Why do I need to filter out reverse duplicates?

In an access control analysis, a conflict between 'Role A' and 'Role B' is identical to a conflict between 'Role B' and 'Role A'. Removing reverse duplicates halves the number of pairs you need to review, saving significant time during compliance audits.

Will the Power Query method update if I add new roles?

Yes. The primary advantage of using Power Query is its dynamic nature. As long as your source data is formatted as an Excel Table, you only need to add the new roles to that table, right-click the Power Query result table, and select 'Refresh'.

Excel & Data Problems