-
Feb 14th, 2018, 09:52 AM
#1
Thread Starter
Junior Member
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
-
Feb 14th, 2018, 10:21 AM
#2
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.
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...
-
Feb 14th, 2018, 10:21 AM
#3
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?
-
Feb 14th, 2018, 03:38 PM
#4
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
-
Feb 15th, 2018, 02:39 AM
#5
Thread Starter
Junior Member
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
-
Feb 15th, 2018, 06:58 AM
#6
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.
-
Feb 15th, 2018, 07:51 AM
#7
Thread Starter
Junior Member
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.
-
Feb 15th, 2018, 08:48 AM
#8
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?
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...
-
Feb 15th, 2018, 10:00 AM
#9
Thread Starter
Junior Member
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.
-
Feb 15th, 2018, 11:30 AM
#10
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|