+ Reply to Thread
Results 1 to 28 of 28

Count the number of duplicates in a given col or range

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Count the number of duplicates in a given col or range

    Hi,

    I have a column of names........say in col-A.

    I am trying to identify the duplicate names (via VBA) and then paste into another column(say column B) the names that appeared more than once.
    I dont need the number of times they appeared just a list of names that appeared more than once.

    Just wondering what the most efficient way to do this .

    any ideas?

  2. #2
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Count the number of duplicates in a given col or range

    hi,

    try this or other way with using Scripting.Dictionary and method key.Exists
    Please Login or Register  to view this content.
    Best Regards
    MaczaQ
    ---------------------------------------------------------------------------------------------------------------------------
    If you are satisfied with the solution(s) provided, please mark your thread as Solved
    If you are pleased with my answer consider to rate it. To thank someone who has helped you, click on the star icon below their name.
    - This way you will add him some reputation points ... thanks in advance.

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Count the number of duplicates in a given col or range

    if you are checking range F1:F25 and the result in cell H1

    Please Login or Register  to view this content.
    the resulting names in column H

    Please Login or Register  to view this content.
    Last edited by snb; 01-13-2012 at 03:52 PM.



  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    ok, this is the 2nd time someone has mentioned this "Dictionary". I tried to understand it once before with out any luck at ALL. So what i decided to do is take your code and modify it to something like the following .........but it does not work. I am sure its because i am doing something wrong.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    snb, i tried your code as well. i used as the input

    from F1 to F15 the numbers
    Please Login or Register  to view this content.

    and what the output was the following



    Please Login or Register  to view this content.

    7,8,9, and 1 shoudl not show up as duplicates

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    snb, i tried your 2nd code which puts things into a col....which is what i really want.......but it did not work at all.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Count the number of duplicates in a given col or range

    Here you go

    If there is no header:

    Please Login or Register  to view this content.
    If there is a header:

    Please Login or Register  to view this content.
    Last edited by Kelshaer; 01-13-2012 at 04:02 PM.
    Regards,
    Khaled Elshaer
    www.BIMcentre.com

    Remember To Do the Following....
    1. Thank those who have helped you by clicking the Star below their post.
    2. Mark your post SOLVED if it has been answered satisfactorily:
    • Select Thread Tools (on top of your 1st post)
    • Select Mark this thread as Solved

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Count the number of duplicates in a given col or range

    well I used strings, like your asked in your question. But what if:
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    Note to all:
    To test the code i have been using Numbers......i did not think this would make a difference. But as SNB pointed out it might.


    Kelshaer , i tried your code......both of them......again using numbers (1 -9 ....from row2 to row100).......i can use names if you want...........but neither of your codes work. Just gives me a bunch of numbers in col B

  10. #10
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Count the number of duplicates in a given col or range

    hi,

    one more way without Collections
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Count the number of duplicates in a given col or range

    Welchs,
    You asked for a macro that will take the duplicate values in Column A and list them in column B.
    I tried the codes that i have posted, both working this way

  12. #12
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    Kelshaer , see enclosed file. Neither of the code snipets you sent work for me. Am i doing something wrong?

    Again, to test it i used "numbers" not names. does this make a difference?

    Collections? I have not used this before. is there an advantage to using this?

    I dont know how many names will be in the column there could be many.......i wont know until i get a sample file. Does this make a difference how its done?
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Count the number of duplicates in a given col or range

    Ok. Try this

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    maczaq ,

    i tried your latest one and it worked.........does it also do well when there are lots of rows of stuff to compare.

    Also, what is the advantage of the collections you mentioned......is it faster?

  15. #15
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Count the number of duplicates in a given col or range

    Try this.

    Please Login or Register  to view this content.
    Last edited by Kelshaer; 01-13-2012 at 08:22 PM.

  16. #16
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    Kelshaer ,

    i tried the latest code and it worked as well. thanks for the heads up about long/integer.




    To all: i plan in reviewing your other code snipets as well.......lots of stuff in them that i dont understand.......and i thought i understood a lot. well maybe not a lot but more than a little.

    Also, any good reading on collections and this dictionary thing keeps coming up......guess save that for another thread.

  17. #17
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Count the number of duplicates in a given col or range

    Mark the thread as SOLVED please

  18. #18
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    Kelshaer , i tried the code you presented on a large data set over 200K rows and it worked in about 1.5 sec.

    I was wondering ........if its not too much trouble if you could just kinda explain what your doing in the code. High level........there are a few things that ........well, i dont understand.

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Count the number of duplicates in a given col or range

    I sent my code because of it's 1-line character.
    If you do not mind using a loop:

    Method 1. using for..each; using a string to store unique elements

    Please Login or Register  to view this content.
    Method 2. using an array; using a string to store unique elements

    Please Login or Register  to view this content.
    Method 3. using for ... each; using a collection to store unique elements

    Please Login or Register  to view this content.
    Method 4. using for ... each; using the dictionary objet to store unique elements

    Please Login or Register  to view this content.
    Method 5: using the function 'Evaluate':

    Please Login or Register  to view this content.
    Last edited by snb; 01-16-2012 at 07:19 AM.

  20. #20
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    thanks snb, your code is always very concise .......thanks!!!!!

    i have not had a chance to review all of your latest post but i will.......lots of stuff to learn here. thanks!

  21. #21
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Count the number of duplicates in a given col or range

    Hello welchs101,

    The attached workbook lists the duplicates using the Dictionary object. There are two command buttons. One loads the sheet with 50,000 numbers ranging from 1 to 10,000. The second list the duplicates and displays the time it took to do so. On my machine, the macro listed about 9,500 duplicates in 0.26 seconds.

    The Dictionary key can be any object type except a User Defined Type. The macro checks if the key is a string and if it is trims the leading and trailing spaces from it. Spaces can cause the same entry to considered unique.

    Dictionary Object Macro to List Duplicates
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  22. #22
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Count the number of duplicates in a given col or range

    I amended my last post to compare several methods.

  23. #23
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Count the number of duplicates in a given col or range

    Hmm ... just to join the mayhem!!! ...

    This can be done without VBa, see sheet "Sheet1 (formula)"

    If you're feeling really masochistic you could translate the formulae into VBa
    Please Login or Register  to view this content.
    Run this macro on "Sheet1"

    Thiese "methods" will work for either numbers or strings, or a combinatoin of both.

    In A2 in either sheet change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Fill Down to test with numbers
    Or roll your own data series.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  24. #24
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Count the number of duplicates in a given col or range

    I tried these:

    in the helper column H:

    in H2:

    PHP Code: 
    =IF(AND(COUNTIF($A$2:$A$20;A2)>1;COUNTIF(OFFSET($A$2;;;ROW()-1);A2)=1);A2;""
    in B2:

    PHP Code: 
    {=IF(ROWS($H$2:$H$20)-COUNTBLANK($H$2:$H$20)>=ROW();INDEX($H$2:$H$20;SMALL(($H$2:$H$20<>"")*ROW(1:19);ROW()-1+COUNTBLANK($H$2:$H$20)));"")} 
    NB. Arrayformula
    Last edited by snb; 01-15-2012 at 11:51 AM.

  25. #25
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Count the number of duplicates in a given col or range

    @snb
    Does your formula in B2 work?
    Try this array formula
    Please Login or Register  to view this content.
    Use this for 2007 and above
    Please Login or Register  to view this content.
    N.B.
    I've put a simpler array formula in E2 for the two helper column pre 2007 solution.
    Attached Files Attached Files

  26. #26
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Count the number of duplicates in a given col or range

    See columns H:I
    Attached Files Attached Files

  27. #27
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Count the number of duplicates in a given col or range

    @ snb
    Ah so you have changed your opinion yet again, what a surprise!!!
    C/V your first formula post
    Please Login or Register  to view this content.
    with your second
    Please Login or Register  to view this content.
    We can all do that, but let's remember that we are trying to help "lesser mortals", not showcase our individual brilliance.
    Was your first offering a mistake or not?
    Test your code/formulae before you post it

  28. #28
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Count the number of duplicates in a given col or range

    thanks for all the info........lots of stuff here to review. thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1