How to sort out duplicates?

Jeff Taylor

How do I search for unique values? I have a list of duplicates and want only the unique values from this list as well as a count of the resulting filter.

thanks

Scott Sumner

@Jeff-Taylor

Not super simple. Some answers here possibly.

Jeff Taylor

thank you. Perhaps better to do in Excel then.

cipher-1024

If you’re working with just a list of values, you can install the CsvQuery plugin and run a SQL type query on your list. Say you have this as your list:

filter1
filter1
filter1
filter2
filter2
filter2
Filter1
filter2
Filter3

Open the CsvQuery window, (you may have to hit the “read file” button), type this into the command bar:

SELECT col1, COUNT(*) FROM THIS GROUP BY COL1 ORDER BY COL1

Click Execute
the output window will show this:
Filter1 1
Filter3 1
filter1 3
filter2 4

If you want case insensitive searches:

SELECT col1, COUNT(*) FROM THIS GROUP BY COL1 COLLATE NOCASE ORDER BY col1 COLLATE NOCASE

That will get you this:
Filter1 4
filter2 4
Filter3 1

I had to use ctrl-c to copy the output. Right click - copy didn’t work for me. Note that in a CsvQuery query “this” is the table name (your list in the active window).
Reading a file with 100k lines and executing the search each took about a second on my test case (the sample above repeated a lot). IDK if you can make a macro out of this, but it’s another option besides regex and python.

May the Sort be with you.