dcsimg
Results 1 to 10 of 10
  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Lightbulb Macro for finding most frequent occuring strings in a list

    Hi everyone,

    I have a project where I am working with really large datasets, and I have to use excel for it.

    One of the tasks to do is to extract most frequently occuring string, second most frequent string, third etc...

    I know that there is a formula to do this, but I would like to keep it simpler and do it with macro..
    Any experts here who can help?

    Thanks in advance

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,265

    Re: Macro for finding most frequent occuring strings in a list

    Define a string (example data).
    Put your algorithm for doing this (how you think it should work)
    Put your coding here and which bit doesnt work.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,481

    Re: Macro for finding most frequent occuring strings in a list

    Are the "strings" all in the same column? Are they the entire cell value, or a subset of them?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,552

    Re: Macro for finding most frequent occuring strings in a list

    use a top 10 (or similar) sql query?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: Macro for finding most frequent occuring strings in a list

    Hello,
    Sorry for incomplete explanation. Data is in one column in excel document. And I would like to have in another column words which are most frequent, second most frequent etc...
    "Words" are consisted of letters and numbers. And they are all in different cells.
    If I can explain more, feel free to ask

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,481

    Re: Macro for finding most frequent occuring strings in a list

    Show us what would be in the one column, and how "words" would be defined based on those values.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: Macro for finding most frequent occuring strings in a list

    Here it is:



    So, I need something which will tell me: If the name is A, the most occuring string is a1, secong most occuring is 1b etc.

  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,265

    Re: Macro for finding most frequent occuring strings in a list

    Pivot it. I think there is an option for max count.
    Otherwise its pivot to get a count and pivot the count for the max per name.

    Alternatively via vba?
    Sort the data by name and string
    do a loop from the first row to last.
    - everytime the name or string changes, hold the name, string and total in an array
    ------if the current total is more than the held one, overwrite the string (if the name is the same) and the total
    ------ reset counts and variables holding the name and string
    - if its the same name and string - add one to the count

    - what happens if the totals are the same?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: Macro for finding most frequent occuring strings in a list

    I need it as a VBA code, but i dont know to code in VBa, that is why I wrote the question here.

  10. #10
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,265

    Re: Macro for finding most frequent occuring strings in a list

    Ok.

    First you need the steps you want to take.
    Second, in excel you have an option of recording a macro. Now the resulting code works, but is NOT the best. It is good to see how it can be done and then learn the objects to properly set up the VBA.

    So, taking my previous post... the steps :
    Start recording a macro (File>options > custom ribbon -> tick developer .... in the dev ribbon click record macro)
    Select your data columns
    do a sort
    Stop the macro
    Click the vb editor button and have a look at what was generated.

    You'll see lots of selection objects.. not the best. but wil work as long as the focus is on excel and doesnt change sheets.
    The you'll need to read up on loops (specifically the for...next loop) ranges (in excel vba) cells (excel vba).

    Then try a chunk of code and post here.

    Are you doing a course or is it for work ?
    If the former - you should have class notes.
    If the latter, do they have a one day course you could take, or internal training?
    If its your own time - i'd recommend looking online for free courses on vba excel with explanations to follow. I think the first couple of sticky posts in this forum have very useful links.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.