How to Remove Duplicate Pairs

[jrjoseph18]

How can I remove one of the two pairs (see highlighted text)?: 

 

Thank you!

[JohnJPS]

Use the Unique tool, and select both columns.

Hope that helps!

[Kenda]

I believe that the unique tool will look for duplicate records in all of the fields you select, but it will look for duplicates in the fields separately. I do not think this will work for your case.

I have attached a workflow that should hopefully help. You would just want to keep what isn't joined in the Join tool (and then separate your fields again).

Let me know if this solves your problem!

[jrjoseph18]

Thank you both for your input!  I am actually looking to keep one of the two pairs.

If I start with:

I want to end with:

I may be able to work off of this engine, though - this is a good start!

Thanks again.

[Kenda]

@jrjoseph18 Sorry about that. I guess I didn't read your original post close enough. What about the attached module? I just added some tools after the Join to keep one of the duplicates. 

[danrh · Accepted solution]

You could also use Transpose and Summarize to deal with the duplicates:

ID each record, transpose so Field1 and Field 2 are in the same column, sort so your values are in the same order for each record, concatenate so each record is back on one row, grab a unique and use text to columns to separate the fields back out. Do a select at the end to drop unwanted columns and rename back to the original.

Note that this might flip-flop what's in Field1 vs Field2.  From your data it didn't look like this was important, but if it is then keep the RecordID in the Select tool and join it back to the data right after the Record ID tool.

Dan

[JohnJPS]

My first response was clearly a bit hasty.  I would still approach this using the "Unique" tool, but I would create a new field "compareField" as follows...

Max(ToString([fld1]),ToString([fld2])) + "_" + 
Min(ToString([fld1]),ToString([fld2]))

... where [fld1] and [fld2] are the fields in question.  Then run Unique on "compareField".

Caveat: this doesn't extend beyond just two compare fields. But it's very fast for just two fields.

[jrjoseph18]

@BarnesK No problem - I appreciate your efforts, and for introducing me to the modulo function!  

[jrjoseph18]

@danrh - Thanks, Dan.  Reordering the pair ID's by adding a record ID, transposing the data, sorting the data by the record ID and pair ID's, then finally finding unique pair ID's is what helped me get what I needed.

[Fgill]

Question: why are you using min and max? Seems like useless processing overhead?  please explain.