Need code to find occurance of word in Spreadsheet

[Johnred]

Hi,

Anyone please help me on this...I want Macro code for this if possible.....
I have a database with few names & i want to know which name occured maximum time in the Database also which is 2nd Max Name in database & Number of occurance...

Thanks,
John

[Sisyphus]

Hi,
This sounds like the kind of task one might solve with the help of filters or pivot tables. What is your reason for wanting a macro to do the job? How often do you need to run this query? What is the output format you require?

[Don]

How will the data be structured? In other words, are the names all going to be in column A or could the be dispersed throughout the worksheet? Can you include a sample worksheet?

But, as Sisyphus mentions, this is easily done through a PivotTable

[Johnred]

Thanks!...

My Spreadsheet Data looks like this...Now I want to know "Which name occurs Maximum Time in the Data & How many time occurs?". Each name is in Different Cell....



1 Smith Johnson Williams Jones Brown Davis Miller Wilson Moore
2 Anderson Thomas Jackson White Harris Martin Thompson Garcia Martinez
3 Clark Rodriguez Lewis Lee Walker Hall Allen Young Hernandez
4 Brown Davis Miller Wilson Rodriguez Lewis Lee Walker Hall
5 Smith Johnson Williams Jones Brown Hall Allen Young Hernandez
6 Clark Thomas Jackson White Harris Martin Brown Davis Miller

Johnred

[Sisyphus]

Hi,
The attached code does what you want but I don't have a good feeling about it. I feel that an easier solution should have been possible without the use of code, either with a pivot table or an array formula. If there is some one out there who knows how to do it, please do step into the ring.

Meanwhile, your names can get sorted using oldfashioned VB code. All of it is in one module which may be called by any name you like. I have left the default name of Module1.
To install it, Open the VB Editor window, either by pressing Alt+F11 or by selecting Tools / Macro / Visual Basic Editor from your CommandBar.
You should see the Project Explorer window on the left of the VBE window. It lists the open workbooks’ work and code sheets. If you don’t see it, open it by selecting View / Project Explorer or by pressing Ctrl+R.
To insert the code module right-click on the name of your VB Project (that is, the name of your workbook). In the dropdown that opens, select Insert / Module. It will be called Module1 by default if there aren’t any other modules with default names already.
Paste the code into this module.
You can call the code from the Tools menu. Press Tools / Macro / Macros.
From the list that opens select “CountNames” and press the Run button.

Except within the attached workbook, don't do that before you have set the constants you see at the top of the code module and copied below here:

Code: Select all

    Const DataSheet As String = "Names"
    Const FirstDataRow As Long = 2
    Const FirstDataCol As String = "A"
    Const LastDataCol As String = "I"
    Const ResultSheet As String = "Occurrences"
    Const FirstResultRow As Long = 2
    Const FirstResultCol As String = "A"


The worksheets "Names" and "Occurrences" must exist. If you want to use other names, make sure that the names on their tabs and in the Constants are identical. First and last columns and first rows must be entered in the constants. The last rows will be found by the macro.
If you have questions, please ask. If not, happy counting!

[Johnred]

Hey!! You rock....

It is working & i am initialy able to do that....will bother you again if will require any other help .

Thank you so much for Help@@

Rgds
Johnred.

[Johnred]

Hey,

It is working amazingly....

But I have a problem, Now if i have more data Points in more columns & Rows then how to increase Data range in Code.

Thanks,
Johnred

[Sisyphus]

Hi,
At the top of the code you have the constants that set the parameters:

Code: Select all

    Const FirstDataRow As Long = 2
    Const FirstDataCol As String = "A"
    Const LastDataCol As String = "I"


You probably didn't change the first row containing your data range. But if you want more of a header in future you can start your range in row 3 or 4 or anywhere you like. The code will find teh end by itself. So, you can't have anything but data below the FirstDataRow.

You probably didn't change the first data column. The constant FirstDataCol points at column "A". But if you want any columns to the left of your data you can change this to "B" or "C" or anything. The character you enter must be upper case.

The parameter you are probably asking about now is the last data column. That is the constant LastDataCol, set to "I" in the example. You can replace the "I" with any character of the alphabet. You must use an upper case character, you can't use twin character (like "AA") and you can't use a column that is before the FirstDataCol.

I hope that helps.