Match Users to Roles in WPS Spreadsheets Only When All Required Rights Exist
Question details
The user needs to generate a CSV-ready list of roles for each user, but only where the user has every right required by that role.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is comparing user permissions against role requirement tables and wants a formula-based, repeatable result instead of manual checks.
- Observed behavior
- Formulas that test only one matching right can assign roles incorrectly because a user may not have all required rights for that role.
Make sure your workbook has three consistent ranges or tables—one for users, one for user rights, and one for role requirements—and that the rights use identical spelling in every range.
Use a dynamic-array formula to return only fully matching roles
Use this when a role must require every listed right and you need one CSV-ready cell per user.
The formula evaluates each role’s complete rights list against the current user’s rights. A role is returned only when no required right is missing; checking only one right can create false matches.
Click the cell where the first user’s CSV-ready role list should appear, for example G2.
Enter: =LET(rngRole,$B$2:$B$100,rngRight,$C$2:$C$100,ur,$F$2:$F$100,roles,UNIQUE(FILTER(rngRole,rngRole<>"")),matched,BYROW(roles,LAMBDA(r,LET(req,FILTER(rngRight,rngRole=r),IF(SUMPRODUCT(--ISNA(MATCH(req,ur,0)))=0,r,"")))),TEXTJOIN(", ",TRUE,matched))
Replace rngRole with the role-name column, rngRight with the role’s required-right column, and ur with the current user’s rights range. Use absolute references or named ranges.
Press Enter, then drag the fill handle down or use Ctrl+D to apply the formula to the remaining users.
Assign Roles by Full Rights Matching in WPS Spreadsheets
Use WPS Spreadsheets to build a dynamic-array formula that checks every required right before assigning a role, giving you a free, formula-driven way to produce CSV-ready role lists.
- 1. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains your user, rights, and role tables.
- 2. Prepare the ranges: Place users, user-rights, and role requirements into separate columns or tables, using consistent spelling for rights.
- 3. Enter the dynamic-array formula: Select the result cell to the right of the first user and enter the LET/BYROW/TEXTJOIN formula, then replace the example ranges with your actual ranges.
- 4. Fill and export: Press Enter, copy the formula down for all users, then copy the result column or save the workbook as .xlsx for a CSV-ready output.
Frequently Asked Questions
Why can’t I just match one right when assigning roles?
If you test only one right, a user with a partial permission set may be assigned a role that requires additional rights. The formula must compare the user’s full rights list with every right required by the role before returning that role.
Can I use this formula in WPS Spreadsheets?
Yes. In WPS Spreadsheets versions that support dynamic arrays, TEXTJOIN, UNIQUE, FILTER, and BYROW, enter the formula in the first result cell and adjust ranges. If your version does not support BYROW, use a helper-column approach.
How do I update the formula to a different table layout?
Edit the three ranges in the LET formula: rngRole is the role-name column, rngRight is the required-right column, and ur is the current user’s rights range. Keep rights spelled exactly the same across tables.
What should I do if the formula returns blank?
Check that the user-rights range truly contains every right required by at least one role, that the function separator matches your regional settings, and that dynamic-array functions are enabled.
How do I make the result CSV-ready?
The formula uses TEXTJOIN with a comma and space, so the returned roles are already comma-separated. You can copy the result cell directly into a CSV text field or remove the spaces after commas if your CSV import requires it.
