How to find duplicates on 3 columns in IDEA

4 posts / 0 new
Last post
William Yong
Offline
Joined: 06/30/2014 - 00:37
How to find duplicates on 3 columns in IDEA

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Offline
Joined: 06/30/2014 - 00:37

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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