How to Remove Duplicate Pairs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Use the Unique tool, and select both columns.
Hope that helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@BarnesK No problem - I appreciate your efforts, and for introducing me to the modulo function!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Question: why are you using min and max? Seems like useless processing overhead? please explain.