Remove reverse duplicate rows

[Cwillson]

Urgh, my brain is starting to hurt on this one!! Need some help from the amazing hive mind please!

I have a table which contains pairs of records, identified using fuzzy logic. Each row contains a potential match, which needs to be manually reviewed.

Each Record has a reference, and the process has matched all records against all records, therefore some rows are repeated but reversed, i.e. Record A ref appears first then Record B, and the same records appear on another row with Record B reference first and Record A second. N.B. There are numerous other columns in my table, which are what need to be manually reviewed once I've whittled the list down to remove reverse duplicates.

I'm trying to find a way to highlight the first pair, irrespective of which direction it's in, so that I can filter out any reverse pairs. I was trying to do this by creating a concatenated GUID for each row A-to-B, and then searching for the reverse B-to-A using a countif statement. This clearly doesn't work though, as both the original and the reverse records will be highlighted.

Perhaps it's something to do with identifying the minimum row number for each pair and it's reverse? As part of the manual review, rows may be filtered and sorted, so row number probably wont work as this will change.

Here's an example of my data...

Book2.xlsx
	B	C	D	E
1	Record A	Record B	A-to-B Concat	B-to-A Concat
2	abc	def	abc|def	def|abc
3	def	abc	def|abc	abc|def
4	abc	fgh	abc|fgh	fgh|abc
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=CONCAT(B2,"|",C2)
E2:E4	E2	=CONCAT(C2,"|",B2)

 

[Cwillson]

In the above example, I'd like to highlight row 2 as being a reverse duplicate of row 1, but leave rows 1 and 3 as needing manual review.

Hope that all makes sense!!

 

[Cwillson]

Not sure it's the most elegant solution, but I've found one....

Adding a GUID column (A) with increasing references, e.g.1, 2, 3 etc enabled me to use this formula in cell F2 to return the GUID for the first entry of a reverse pair;

=IFERROR(MIN(INDEX($A$2:$A$4,MATCH(D2,$E$2:$E$4,0),1),INDEX($A$2:$A$4,MATCH(E2,$E$2:$E$4,0),1)),A4)

I then used this formula in cell G2, to highlight the first instance of each unique row;

=IF(COUNTIF($F$2:$F$4,F2)>1,IF(COUNTIF($F$2:F2,F2)=1,"Y",""),"Y")

Copying down these formula in cells F2 and G2 allows me to filter for "Y" in column G - the unique pairs which require manual review!

Very happy if someone can propose an improvement though!