+ Reply to Thread
Results 1 to 34 of 34

Find count of unique entries using vba

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

    Find count of unique entries using vba

    Hi, see enclosed file. in the file in cols a and b..........are what my input file looks like. THe other cols to the right are what i want my output to look like.

    Basically, i have two cols...one a "protocol" col and another a "site" col.

    I want to know for a given protocol what is the number of "unique" sites.

    Note: I would like to use vba to do this as i am reading in the file and i just want to process it........

    Note: I also am familiar with dictionaries if you want to use them but i could not figure out how to use them in this case.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Find count of unique entries using vba

    Try this code
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find count of unique entries using vba

    Another one FWIW

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find count of unique entries using vba

    Please Login or Register  to view this content.
    Last edited by jindon; 11-19-2019 at 02:57 AM.

  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Find count of unique entries using vba

    How about without using a Dictionary, a loop nor any variables...
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi,

    can be also achieved just with a couple of codelines via an Excel Basics : an advanced filter …

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

    Re: Find count of unique entries using vba

    I am always amazed at how little i really know about all this macro stuff. I looked at all 4 of these responses and i dont really understand any of them.

    I am going to try and review each one because i think it will be a good learning exercise.

    I am going to start with Jindon since you have sent you were the one who taught me "dictionaries" ...can you provide a litte
    more detail into what you're doing in your code. I tried putting "stop" in the code to see whats going on but that did not help.

    Also, if all of you could provide a little more detail i would "greatly" appreciate .....i really do thanks.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find count of unique entries using vba

    Based on the condition, the result will never exceed the number of rows the original data has, so we can use one array for loading data and also for outputting as a result.
    Please Login or Register  to view this content.
    HTH.

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

    Re: Find count of unique entries using vba

    Jindon, thanks for the info. i am reviewing as i have time. i did have one question......why is it when i stop the code at various places that i cant see the "dictionary" information.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find count of unique entries using vba

    OK, Variables are used for the purpose of Local Window.
    Please Login or Register  to view this content.

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

    Re: Find count of unique entries using vba

    AlphaFrog:
    Can you explain your code in a litte more detail.
    Specifically what are the following statments doing:
    (1) Set dSite=.Item(v(i,1))
    (2) dSite.Item(Cstr(v(i,2)))=1

    Jindon:
    What is this doing=>"w(1)(temp) = Empty"

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find count of unique entries using vba

    Please Login or Register  to view this content.
    That is a sub-dictionary.

  13. #13
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find count of unique entries using vba

    Quote Originally Posted by welchs101 View Post
    AlphaFrog:
    Can you explain your code in a litte more detail.
    Specifically what are the following statments doing:
    (1) Set dSite=.Item(v(i,1))
    (2) dSite.Item(Cstr(v(i,2)))=1

    Jindon:
    What is this doing=>"w(1)(temp) = Empty"
    Jindon and I used similar methods; a dictionary of dictionaries.

    (1) Creates a site dictionary for a given protocol
    (2) Add a site to that site dictionary

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

    Re: Find count of unique entries using vba

    jindon, i went through your code and had some questions...please see below:


    Please Login or Register  to view this content.

  15. #15
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Find count of unique entries using vba

    Bit late into the mix, but since you mentioned...
    Note: I would like to use vba to do this as i am reading in the file and i just want to process it........
    I took it as Book1.xlsx is separate file from where processing takes place.

    It that's the case, you can also use ADO like below.
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

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

    Re: Find count of unique entries using vba

    ok, from what i have learned i am trying to create my "OWN" code. But i am having issues. First issue is shown below....

    Find this in the code
    " '********Get Run time error on this line......**********
    'I think its due to "dic_protocol_unique.Item(protocol_array(LC1, 1))(1).Item(site_array(LC1, 1))(1) + 1"
    'but i dont know why
    '****************"




    Please Login or Register  to view this content.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find count of unique entries using vba

    1)
    'why clear variable w(1)????
    ReDim w(1)
    You can put that out like this, if you like
    Please Login or Register  to view this content.
    w(0) is a newly assigned row reference for a particular item of "Protocol" within the array a.

    2)
    'a(w(0),1)=a(row,1)= protocolnumber value
    'a(i,1) is same.....a(row,1)=protocolnumber value
    'why do this ?????? does not make sense
    a(w(0), 1) = a(i, 1)
    w(0) holds new row reference for the particular data within the array, whereas i is always going down the original data table.

    3)
    'why assign a(w(0),2)=a(row,2)=SiteID value....a value of "0"
    a(w(0), 2) = 0
    It is initializing the count of newly appeared "Protocol".

    4)

    '????why assign dictionary to "w"....what exactly does "w" now look like???
    w = dic(a(i, 1))
    w(0) = newly assigned row reference within array a, w(1) = child Dictionary object for counting unique "SiteID" for data in a(i,1).
    '?????why assign a(row,2)=a(row,2)+1??????
    a(w(0), 2) = a(w(0), 2) + 1
    Add + 1 when new unique "SiteId" appears.
    '?????Why do this below...what is this doing?????
    w(1)(temp) = Empty
    w(1) is a child dictionary to keep unique "SiteId" within a particular "Protocol", it is just adding the item to the child dictionary when new "SiteID" appears.

    '?????Why do this below...what is this doing?????
    dic(a(i, 1)) = w
    Load the array w back to main Dictionary for the particular "Protocol".

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

    Re: Find count of unique entries using vba

    What about the macro i created........can someone tell me why that line causes a problem?

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

    Re: Find count of unique entries using vba

    jindon, if you have a min can you take a look at the code i put up....i am getting a runtime error at the location i specified and i dont know why/how to fix it.

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

    Re: Find count of unique entries using vba

    question for forum: should i post my question about my code run time error in a different thread or keep it in this one

  21. #21
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Find count of unique entries using vba

    I am curious... did the code I posted back in Message #5 work for you or not?

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

    Re: Find count of unique entries using vba

    hi rick, i have not checked yours yet as i was looking for a "dictionary" soln but i am not understanding the dic on dic solns so i may have to try something else like yours perhaps.

    i really hope someone with dictionary knowledge will review my post with my code and tell me what i am doing wrong.

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

    Re: Find count of unique entries using vba

    could really really really use someones help in figuring out my issue. I am lost dont know what else to try.

  24. #24
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find count of unique entries using vba

    Please Login or Register  to view this content.
    You create two separate dictionaries at the start of your code; one for Protocols and one for Sites. That won't work. You need to create a new site dictionary for each new protocol. So when you test if a protocol exists in the Protocol dictionary, if not exists create a new site dictionary and add the protocol key and newly created dictionary-site item. If a protocol already exists, add the site to its already existing site dictionary. Does that make sense?
    Last edited by AlphaFrog; 11-20-2019 at 08:49 PM.

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

    Re: Find count of unique entries using vba

    So i looked at your code again after reading your comment and i changed my code to what you see below.
    But i still get the same run time error at the same place. I am doing something just fundamentally wrong.


    Please Login or Register  to view this content.

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

    Re: Find count of unique entries using vba

    alphafrog i read your message and i updated my code based on what you said but i still get a runtime error.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find count of unique entries using vba

    Please Login or Register  to view this content.
    No matter how you use it, it will be global to entire "Protocol", means not unique to each "Protocol".

  28. #28
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find count of unique entries using vba

    Please Login or Register  to view this content.

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

    Re: Find count of unique entries using vba

    alphafrog,

    Arent these two equal:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Also:

    Please Login or Register  to view this content.
    is the above code basically saying the key is "site_array(LC1, 1)" and that the value stored is ...nothing=Empty

    How does one get the number of sites out of the dictionary.......is it with the .count function?

  30. #30
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Find count of unique entries using vba

    Almost equal; the 1st one is adding an object (dictionary) directly, the other is adding an array with one element (a dictionary). You can't directly add an object using the second method, but to get around that limitation, you can add an array with one object-element in the array.

    Empty?
    Yes.

    Counts?
    Yes. See my original code in #3

    Jindon is a very clever coder. He kept a running total using an array. I extracted the count and lists from the dictionaries. Not quite as elegant as Jindon, but maybe more understandable for someone new to dictionaries.

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

    Re: Find count of unique entries using vba

    Alphafrog:
    Not sure i understand this statement "You can't directly add an object using the second method, but to get around that limitation, you can add an array with one object-element in the array."
    Are you saying that what i have will work and is correct? Are you saying that i could not have two elements in the array like something below:



    Jindon/ALphafrog:
    any idea why my program has an error at the following step? I am just at a loss.

    Please Login or Register  to view this content.

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

    Re: Find count of unique entries using vba

    this post kinda goes along with my previous post in trying to understand why that statement fails.

    I am enclosing a file really just the same as the other i enclosed but this one has two macros
    1-where run time error at the location i mentioned before.
    2-where same statement but no run time error........and i cant figure out why.............
    Attached Files Attached Files

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

    Re: Find count of unique entries using vba

    I figured out why the runtime error ocurred:

    See code below:
    Please Login or Register  to view this content.
    Basically, i am trying to reference something in a dictionary that is not in the dictionary ...yet.

  34. #34
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Find count of unique entries using vba

    Options...so many options...That's why I love this Forum...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count unique entries based off unique criteria in another column
    By hambly in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-11-2014, 03:33 PM
  2. Count Unique Entries
    By tangomj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2007, 11:58 AM
  3. [SOLVED] Count unique entries
    By Max in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 09:05 PM
  4. [SOLVED] Count unique entries
    By BeSmart in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. Count unique entries
    By Max in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 06:05 AM
  6. [SOLVED] Count unique entries
    By Max in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 09-06-2005, 05:05 AM
  7. [SOLVED] Count unique entries
    By BeSmart in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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