WPS Spreadsheet: Find Reversed First- and Last-Name Pairs in Excel
Question details
The user needs to identify rows where the first-name and last-name values are reversed compared with an earlier row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet stores first names in column A and last names in column B, with data beginning in row 2. The user wants to flag reversed name combinations without concatenating the columns.
- Observed behavior
- Rows containing a first-and-last-name pair in the opposite order are not yet marked for review.
Confirm that first names are in column A, last names are in column B, and row 1 contains headers. Make sure the data range begins in row 2 so the expanding COUNTIFS references align correctly.
Use COUNTIFS to flag reversed name pairs
Use this helper-column formula when you want a TRUE/FALSE result that can be filtered or reviewed alongside the source data.
The formula compares the current row's first name and last name with earlier rows in opposite positions. It returns TRUE when the reversed pair has already appeared in the data.
In an empty column beside the names, select the cell for row 2, such as C2.
Type =COUNTIFS($A$2:A2,$B2,$B$2:B2,$A2)>0 in C2, then press Enter.
Drag the fill handle from C2 down through the remaining rows, or copy C2 and paste it into the rest of the helper column.
Filter the helper column for TRUE values to display rows whose first and last names reverse an earlier combination.
Highlight reversed pairs with conditional formatting
Use this method when you want reversed combinations highlighted directly in the worksheet instead of adding a helper column.
Detect Reversed Name Combinations in WPS Spreadsheet
WPS Spreadsheet lets you compare first- and last-name columns with COUNTIFS, then flag matches with a helper column or highlight them using conditional formatting. The workflow works directly with Excel-compatible worksheets.
- 1. Open the worksheet: Launch WPS Spreadsheet and open the workbook containing the first-name and last-name columns.
- 2. Enter the COUNTIFS rule: In a helper cell beside row 2, enter =COUNTIFS($A$2:A2,$B2,$B$2:B2,$A2)>0.
- 3. Copy the formula down: Fill the formula through all data rows to identify reversed combinations.
- 4. Filter or highlight results: Filter for TRUE values, or create a Home > Conditional Formatting rule using the same formula.
Frequently Asked Questions
Which formula identifies a reversed first-and-last-name pair?
For data beginning in row 2, enter =COUNTIFS($A$2:A2,$B2,$B$2:B2,$A2)>0 in a helper column. The formula returns TRUE when the current pair matches an earlier row in reverse order.
Can I highlight reversed names without adding a helper column?
Yes. Select the name range, open Home > Conditional Formatting > New Rule, choose a formula-based rule, and enter =COUNTIFS($A$2:A2,$B2,$B$2:B2,$A2)>0.
What should I change if my names are in different columns?
Replace every A reference with the first-name column and every B reference with the last-name column. Keep the starting row, such as row 2, consistent in both the formula and the selected range.
Why does the formula use expanding ranges?
The ranges $A$2:A2 and $B$2:B2 expand as the formula is copied down, allowing each row to compare its reversed pair with the rows above it.
