See All records that are duplicates, including the original unique record

I'd like to see all rows that have duplicate values in the same column. But, I don't want to just see the duplicates (usually using the Unique function) but see the the original unique one, and the duplicates thereafter. Afterwards I would have two data sets, one with content that is never duplicated in the data, and a data set of all content that is duplicated, including the original.

How would I do this? Thanks!

Tips and Tricks

Common Use Cases

Preparation

Accepted answers

cmcclellan

I would still recommend using the Unique tool. It gives you the first unique on one output and the rest of the duplicates on the other output. You can then join together to get all the records ... or just add tools from before the Unique tool

All comments

cmcclellan

DavidP

I like @cmcclellan's idea. Here's how I'd do it

duplicates.yxmd

InfoManager

Thanks, DavidP. I think I might still be missing something. The last filter that gets me all the dupes, doesn't get me the original unique row that is later duplicated since only the duplicated rows have the "y" in the last column. I wonder if I just remove the initial Unique tool and just start with the Group by function, if that would solve the problem...I think I would then be able to get ride of the join and union...

InfoManager

Nope! That didn't work. I'll keep playing with it. I'm definitely missing something...

estherb47

Hi @InfoManager

I'd use the Crew Macro's Only Unique tool, if you're able to download. See @DavidP 's post for the link.

But, if you need you can approximate that tool using some Unique, Joins, Summarizes. By Joining the U output back to the D output of Unique, you'll get all of the truly unique (no dupes) out of the L output. Then, easily repeat the first instance of each duplicate using the Summarize (you could also sample, grouping on all of the fields and setting N to 1). Added a field to qualify as original (value of 1) or copy (value of 2). Unioned and sorted.

The Only Unique macro does all of this for you. It's fantastic!

ManualOnlyUnique.yxmd

Tommy1

I had this issue and simply used the Unique tool, a formula, and Union

Duplicates with original data.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703