+ Reply to Thread
Results 1 to 19 of 19

Get unique records by first column using dictionary

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

    Get unique records by first column using dictionary

    Hello everyone
    I have the following working code
    Please Login or Register  to view this content.
    The code loops through the rows and based on unique entries in column A get all the records for similar IDs and put the results in G2 as start point
    I am sure it can be compacted with more intelligent approach .. Can you provide me with the compact version so as to make use of the smart approaches?
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

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

    Re: Get unique records by first column using dictionary

    sample file...
    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!!!

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

    Re: Get unique records by first column using dictionary

    Any data in range("A2:E10") .. In column A put some IDs 100,200,300 ..

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    You guys are only copying the idea from someone else... No unique idea of your own...

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

    Re: Get unique records by first column using dictionary

    We are learning from you Mr. Expert Jindon

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

    Re: Get unique records by first column using dictionary

    We are learning from you Mr. Expert Jindon
    If this is the case and the initial code was jindon's then there can be no improvement!

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    Then why don't you try it for yourself?

    More compact is not always faster/better code.

    Like don't split, just use TextToColumns at the end is one of the method.
    However, adding sting to a string is slow down the code when length get grow beyond certain point.

    Choose better way to the appropriate method is the question.

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

    Re: Get unique records by first column using dictionary

    Thanks a lot.

  9. #9
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    737

    Re: Get unique records by first column using dictionary

    Not sure if performance would be improved but this is how I'd do it based on my understanding of the initial code

    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

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

    Re: Get unique records by first column using dictionary

    Thanks a lot nankw83 for the positive sharing.
    Best Regards

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    Like this
    Please Login or Register  to view this content.
    This will not work when any one element exceeds 255 characters...

  12. #12
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    737

    Re: Get unique records by first column using dictionary

    @YasserKhalil ... You are welcome. Iím learning as well

    @jindon ... Thanks for pointing that out for me, I wasnít aware of it. I guess this is just another limitation of Application.Transpose. What alternative do you suggest to use ?

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    Obviously Transpose is the one to avoid for this kind of procedure.
    Use original array to store all the string, so that you can output at one time regardless of the length of the string.

  14. #14
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    737

    Re: Get unique records by first column using dictionary

    Excellent. Thanks for your valuable input

  15. #15
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    737

    Re: Get unique records by first column using dictionary

    @jindon, I put a small excel file (attached) to test & see what is the error message/behavior excel will show but I didn't get any & it works Did I misunderstand your comment in post #11 ?

    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    I'll have a look at it tomorrow.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    Possibly a version difference, so I guess it updated the limit.

    Your code errors "Type mismatch" on 2013, even the code below doesn't work.
    Please Login or Register  to view this content.
    Can you just try
    Please Login or Register  to view this content.
    and see if the msgbox shows correct figure?

  18. #18
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    737

    Re: Get unique records by first column using dictionary

    Thanks jindon for your feedback. It seems that it is indeed related to the version. Looks like Microsoft has fixed the 255 character limit as it is working flawlessly in my excel. Regarding the second code, I was testing it the other day as I read about transpose limitation of 65k records HERE & it seems that in older version you get an error whereas now the user doesn't even though excel doesn't provide the expected results ! If you run the transpose with anything below 65,536 it works as expected once the size exceeds the aforementioned number of records you get messed up data with many #N/A as show in the attached file.

    By the way, when I run your 2nd code, I don't get any error but I get 34,465 & strangely enough if I run your code with 70k I get 4,466 !

    Thanks for the clarification as it seems that we learnt that Microsoft has fixed one thing
    Attached Files Attached Files
    Last edited by nankw83; 05-26-2020 at 02:26 AM.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,814

    Re: Get unique records by first column using dictionary

    Yeah, I knew that already, thanks.

    So, it still works properly less than 2^16, but the limitation of 255 characters are expanded.

    PS. It returns Actual Upperbound Mod (2 ^ 16 -1) without Error, and that's a problem.
    Last edited by jindon; 05-26-2020 at 04:15 AM.

+ 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] How to count unique records in a column?
    By Rev12 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-21-2018, 03:03 PM
  2. [SOLVED] count unique records in one column based on criteria in a different column
    By rxg2669 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-10-2015, 12:38 AM
  3. Count unique records in a column
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2013, 11:51 AM
  4. [SOLVED] Unique Records in a Column with a twist
    By ciaran01 in forum Excel General
    Replies: 9
    Last Post: 01-15-2013, 05:25 PM
  5. filter unique records with its corresponding column
    By jungelbobo in forum Excel General
    Replies: 1
    Last Post: 05-11-2010, 08:34 AM
  6. Formula to count unique number of records in a column
    By stats09 in forum Excel General
    Replies: 3
    Last Post: 03-22-2010, 10:15 PM
  7. Replies: 2
    Last Post: 01-13-2005, 04:06 PM

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