Search code examples
excelexcel-formulaexcel-2013

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

example

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

Solution

  • Try something along the lines using the following formula:

    enter image description here

    • 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:

    enter image description here

    Test Case Two:

    enter image description here

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

    enter image description here

    • 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.