A quicker way than INDEX to count unique values in a list

  • Hi all,


    I am using the formula =INDEX($A$2:$A$5800, MATCH(0, COUNTIF(L1:$L$1, $A$2:$A$5800)+IF(COUNTIF($A$2:$A$5800, $A$2:$A$5800)>1, 0, 1), 0)) to create a list of any duplicate values in the range A2:A5800.


    This takes a really long time for excel to process...is there an easier (quicker) way? I played with a pivot table which I am new to, but couldn't work out how to filter only if a value was a duplicate.


    Cheers,


    Tom

  • Re: A quicker way than INDEX to count unique values in a list


    Hello,


    Have you tried from the Main Menu : Data > Remove Duplicates ...?


    or use a macro with dictionary object ...


    see http://www.ozgrid.com/forum/showthread.php?t=203119&p=787014#post787014


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: A quicker way than INDEX to count unique values in a list


    Quote from tac_79;795152

    Don't I feel like the fool...never knew that existed! Thanks so much!


    We are all on a trip to keep on learning every single day ...:wink:


    Glad this could help you out ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)