Distinct values with two columns

[CyrilB]

Hi there,

I have something simple to do but I only find complexs way to do it so I must miss something.

Let Say I have two columns TYPE AND TYPE2 which will contain the same domain values.

I want to have the distinct value whatever the order of the values.

TYPE        TYPE2

AAA           BBB

AAA           CCC

BBB           AAA

BBB           CCC

CCC          BBB

At the end I want only the distinct combinaisons :

TYPE        TYPE2

AAA           BBB

AAA           CCC

AAA           CCC

BBB           AAA

BBB           CCC

CCC          BBB

Any ideas ? 

Thanks

[jdunkerley79 · Accepted solution]

You can do this using the Unique Tool combined with a bit of unioning!

Use a select tool to create a set with Type and Type2 reversed

Then union and finally Unique tool

I take it back. Use a formula tool to prepare a new column:

IIF([Type]<[Type2],[Type] + [Type2],[Type2] + [Type])

Do the unique on that then think will be fine.

Attached workflow showing what I mean

[CyrilB]

No that's the first thing I tried but it doesn't recognize the cross references. It's just a select distinct 

Unique tool results are 

TYPE        TYPE2

AAA           BBB

AAA           CCC

AAA           CCC

BBB           AAA

BBB           CCC

CCC          BBB

[jdunkerley79]

yep adjusted my reply - agree immediate first guess wouldnt work

[CyrilB]

I should be able to manage it with your second proposal even without the unique tool, I can simply take the first occurence of your formula result.

Thanks I'll confirm shortly

Kind regards