How to find duplicates on 3 columns in IDEA

William Yong

Forums

Hi there,
I have a file where my task is to extract and flush out the duplicates with 3 columns in IDEA. Any chance of how to do that? I try use @compif, @list and @match, but the result is not what I'm expected. Here's the scenario:

 
Col1
Col
Col3

Row1
1
0
0

Row2
2
2
0

Row3
3
0
3

Row4
4
4
0

Row5
5
0
0

The condition is, to extract duplicate if at least 2 are met, i.e looking back at the table, Row2, Row3 & Row4 will be extracted out. Another catch is, to exclude those with '0'. 
Could anyone share your thoughts here.
Many thanks.
William

Brian Element Wed, 04/22/2015 - 07:17

Hi William,

This seems to work, might be a more efficient way to do this but here is one option:

(COL1 = COL2 .AND. (COL1 <> 0  .OR. COL2 <> 0)) .OR. (COL1 = COL3  .AND. (COL1 <> 0  .OR. COL3 <> 0)) .OR. (COL3 = COL2  .AND. (COL3 <> 0  .OR. COL2 <> 0))

 

William Yong Wed, 04/22/2015 - 23:36

Thanks Brian,
It works. But I believe, the expression could only works for 3 column and if there are additional column, the expression could look messy and complicated. Thereore, I'm now working with a script that could append all files in a folder and perform the overlap or duplicate among all the files.
Rgds,
William

Brian Element Thu, 04/23/2015 - 07:47

Yes, this expression could only do this for 3 columns, if you have more than three you could probably do this through a loop but you would have to create a custom function to do it.  Also you should be able to easily perform this through a script.

Thanks

Brian