How to sort out duplicates?



  • 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



  • @Jeff-Taylor

    Not super simple. Some answers here possibly.



  • thank you. Perhaps better to do in Excel then.



  • 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.


Log in to reply