WPS Spreadsheet: Highlight Duplicate Teams with Reversed Employee Order
Question details
The user needs to identify duplicate two-person teams when the same employees appear in either order.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains two employee columns for each team, and conditional formatting should flag teams that are repeated with the employee order preserved or reversed.
- Observed behavior
- Duplicate teams are not reliably identified when the employees appear in opposite columns or in a different order.
Confirm which two columns contain the employees in each team and identify the full data range to format. If the formula uses named ranges such as Employee_1 and Employee_2, verify that both names refer to the corresponding employee columns.
Use COUNTIFS conditional formatting to highlight reversed duplicate teams
Use this solution when each row contains a two-person team and you want to highlight every team that appears more than once in either employee order.
The formula counts the team in its original order and then counts it again with the employee columns reversed. If the combined count is greater than 1, conditional formatting highlights the duplicate team.
In WPS Spreadsheet, select the two employee columns for the data rows, such as F2:G100.
Go to the Home tab, choose Conditional Formatting, and select New Rule or a formula-based rule option.
Enter =COUNTIFS(Employee_1,$F2,Employee_2,$G2)+COUNTIFS(Employee_2,$F2,Employee_1,$G2)>1. Replace Employee_1 and Employee_2 with the correct ranges or named ranges if your worksheet uses different names.
Set a fill color or other formatting style, confirm the rule, and apply it to the selected team range.
Create a normalized team key before finding duplicates
Use a helper column when you need a reusable team identifier for filtering, sorting, reporting, or creating a unique list of teams.
Highlight Duplicate Teams in WPS Spreadsheet Without Reordering Employee Data
WPS Spreadsheet can apply a COUNTIFS-based conditional formatting rule that checks both employee orders, so reversed two-person teams are highlighted without changing the original worksheet.
- 1. Open the worksheet: Launch WPS Office, open your spreadsheet, and select the two employee columns containing the team records.
- 2. Create a formula rule: Choose Home > Conditional Formatting > New Rule, then select the formula-based rule option.
- 3. Check both employee orders: Enter =COUNTIFS(Employee_1,$F2,Employee_2,$G2)+COUNTIFS(Employee_2,$F2,Employee_1,$G2)>1, adjusting the cell references to your data.
- 4. Apply the formatting: Choose a highlight style, confirm the rule, and review the selected range for repeated teams.
Frequently Asked Questions
Why does the duplicate formula use two COUNTIFS functions?
The first COUNTIFS checks the employee order as entered, while the second checks the reversed order. Adding both counts makes the rule order-independent.
Where should the conditional formatting formula be entered?
Select the full two-column team range, open Home > Conditional Formatting > New Rule, and enter the formula using references to the first row of the selected range, such as $F2 and $G2.
Can I use regular cell references instead of Employee_1 and Employee_2?
Yes. Replace the named ranges with the employee-column ranges, for example $F$2:$F$100 and $G$2:$G$100, while retaining the reversed pair in the second COUNTIFS.
How can I create a list of unique teams without considering order?
Create a normalized helper key by sorting the two employee names within each row, for example with =TEXTJOIN(",",TRUE,SORT(F2:G2)), then use Remove Duplicates or UNIQUE where supported.
