Only Unique Macro

While adding "All" and "None" buttons to the unique tool a couple of weeks ago, I was reminded of a macro which I have found useful on occasion but not yet shared on this blog.  The Only Unique Macro:

pictured here with the standard Unique Tool.  

The Unique Tool is great for ensuring that a set of fields is unique, however when you have a set of fields you expected to be unique but aren't, working out why is a little tricky.  

The reason for this is say you have five identical duplicate records (on the selected fields) and you want to look at all of those records together (perhaps to see what is in the other fields), one of the five records is in the unique output stream and the other four are in the duplicates output stream.  If you have a million other records in the unique output stream, finding that one can be hard.

Enter the "Only Unique" macro.  The working of which is as follows:

• If there is exactly one record with a combination of the selected field values it comes out of the unique stream. So the unique output only contains the completely unique records.
• If there are records with duplicates of the selected field values, then ALL of those records come out of the duplicate stream.

You can download the macro and above sample module here, and look out for this macro in the next release of the Chaos Reigns Within (CReW) macro pack.

If you want to challenge yourself, then try to build the described macro without looking how I did it.  I suspect there are probably a lot of different approaches you could take.

I hope you find it useful.