In excel, how do i hide duplicate value of Column A & B (where AB=BA)

Question

In my data there are 3 columns.

Where I want to show unique route only.

Data

Location-1	Location-2	Distance
AAA	BBB	10
BBB	CCC	20
CCC	DDD	30
BBB	AAA	10
DDD	CCC	30

Target is.

Location-1	Location-2	Distance
AAA	BBB	10
BBB	CCC	20
CCC	DDD	30

Thank you!

I want to show unique values only.

Note: I am using Excel-2013

Answer 1

Try something along the lines using the following formula:

---

• Formula used in cell E2

=LET(
     a,A3:B5,
     b, REDUCE(A2:B2,SEQUENCE(ROWS(a)),LAMBDA(c,d,VSTACK(c,SORT(INDEX(a,d,),,,1)))),
     UNIQUE(HSTACK(b,C2:C5)))

---

Test Case One:

---

Test Case Two:

---

Since OP has updated that their version of Excel is 2013, therefore here is a solution, which works from Excel 2010+ onwards.

---

• Formula used in cell E3

=IFERROR(INDEX($A$3:$C$7,AGGREGATE(15,6,
 (ROW($A$3:$A$7)-ROW($A$3)+1)/(IFERROR(IF($A$3:$A$7&"|"&$B$3:$B$7<>"",
 MATCH($A$3:$A$7&"|"&$B$3:$B$7,$B$3:$B$7&"|"&$A$3:$A$7,0)),
 ROW($A$3:$A$7)-ROW($A$3)+1)>=ROW($A$3:$A$7)-ROW($A$3)+1),
 ROWS(E$3:E3)),MATCH(E$2,$A$2:$C$2,0)),"")

---

Notes: One needs to hit CTRL+SHIFT+ENTER while exiting the edit mode for the approach which supports Excel 2010 onwards as well as needs to fill down & fill right accordingly, also ensure to change the cell references and ranges as per your suit.

---

Answer 2

Remove Specific Duplicates

=LET(data,A2:C6,sCol,1,eCol,2,dlm,"@",
    start,CHOOSECOLS(data,sCol),
    end,CHOOSECOLS(data,eCol),
    ds,start&dlm&end,
    de,end&dlm&start,
    s,SEQUENCE(ROWS(ds)),
FILTER(data,IFNA(XMATCH(ds,de),s)>=s))

Answer 3

In column D (starting in cell D2) enter the following formula:

=CONCAT(SORT(A2:B2,,,TRUE))

Then select the entire cell range, go in the ribbon to Data -> Data Tools -> Remove duplicates and select column D only.