Hi @ all,

I am facing a problem. I have an table like this:

A D
B E
C F
.. ..
D A
E B
F C

I need only the value combination once, like this:

A D
B E
C F

How can I do this?

The simple "select distinct col1, col2" does not work, because it considers the combination and not the value.

Can anyone help me with this?

Thanks in advance

First create a third column that is the transposition of the second column

1st column

A D

2nd Column

D A

3rd Column

A D

Secondly create a 4th column that states

Alias:IIF(3rd Column = 1st column, Duplicate, Unique)

Then only report on the unique columns.

David

Hi David,

the issue that Im dealing with two columns:

Column 1 Column 2

A D
B E
C F
.. ...
D A
E B
F C

Sorry, I should have described the table better.

If you read my post you will see that I have examined cols 1 and 2

By reversing your second column in column 3 you are replicating the first column. So if you compare the new 3rd col to the original you can test for duplicates.

David

If you read my post you will see that I have examined cols 1 and 2

By reversing your second column in column 3 you are replicating the first column. So if you compare the new 3rd col to the original you can test for duplicates.

David

Hi David,

unfortunately I don't understand your advice.

If I add an third column and put the values of the first column in it, I will get following structure:

column 1 column 2 column 3
A D A
B E B
C F C
..
D A D
E B E
F C F

What is exactly the next step? The comparison of column 1 and column3?

Can solve this 2 ways

1)
Make a lookup table with 2 fields to lookup the field values and return only one type...

2)
Sort the order of the columns values....
SortedCol1: IIF(Col1 < Col2; Col1;Col2)
SortedCol2: IIF(Col1 < Col2; Col2;Col1)

This way both AD and DA will show up in order of AD in your sorted columns.
Then distinct them and your done.

Can solve this 2 ways

2)
Sort the order of the columns values....
SortedCol1: IIF(Col1 < Col2; Col1;Col2)
SortedCol2: IIF(Col1 < Col2; Col2;Col1)

This way both AD and DA will show up in order of AD in your sorted columns.
Then distinct them and your done.

I've choose the second way and it works. Thank you very much !!!

I've choose the second way and it works. Thank you very much !!!

LOL You almost sound surprised :eek: