Macro for finding most frequent occuring strings in a list

[nn92]

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

[Ecniv]

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.

[vbfbryce]

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

[westconn1]

use a top 10 (or similar) sql query?

[nn92]

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

[vbfbryce]

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

[nn92]

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.

[Ecniv]

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?

[nn92]

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

[Ecniv]

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.