In my data there are 3 columns.
Where I want to show unique route only.
Location-1 | Location-2 | Distance |
---|---|---|
AAA | BBB | 10 |
BBB | CCC | 20 |
CCC | DDD | 30 |
BBB | AAA | 10 |
DDD | CCC | 30 |
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
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.