I have data available in excel as given below:

Below is the criteria for the expected column:

Duplicate relationship should be color coded/flagged.

I have added expected result in a column G Is duplicated?.

To achieve that I have tried using Match function of excel, but it doesn't match my requirement.

Please suggest what should be the correct approach to fix this.

CodePudding user response：

You can create a sorted csv of each of the words on each row:

=TEXTJOIN(",",FALSE,SORT($A3:$D3,1,1,TRUE))

name	relation	name	relation	sorted_csv
Milly	Wife	Jack	Husband	Husband,Jack,Milly,Wife
Jack	Husband	Milly	Wife	Husband,Jack,Milly,Wife
Reacher	Son	Jack	Father	Father,Jack,Reacher,Son
Reacher	Son	Jack	Mother	Jack,Mother,Reacher,Son

Then you can count the rows by sorted_csv:

=COUNTIF($E$3:$E$6,$E3)

name	relation	name	relation	sorted_csv	count by sorted_csv
Milly	Wife	Jack	Husband	Husband,Jack,Milly,Wife	2
Jack	Husband	Milly	Wife	Husband,Jack,Milly,Wife	2
Reacher	Son	Jack	Father	Father,Jack,Reacher,Son	1
Reacher	Son	Jack	Mother	Jack,Mother,Reacher,Son	1

Any row that has a count greater than 1 is a duplicate of another row.