Creating a Macro for Finding the words within a Dynamic List in a workbook and compiling a Report of the same

[brijeshdeshpande]

This is a first time I have registered for any of the forums online. I have been working with Excel for quite sometime but only with the Formulae & Formatting and very little with Macros. I have achieved all my goals with the above, but this task has been a bit confusing for me and has confused me a lot and I am sure such a thing cannot be done through formulae and if it can be then i possibly have lost patience.

What I need? is a Macro for Finding words within a Dynamic List (which will be updated with new words and redundant words shall be removed on weekly basis) in a given workbook (within some specific columns on all Sheets) and compiling a report of their count of instances of appearance in all this Data. After the count is generated a Pareto Graph has to be generated on the basis of these counts (i am well versed with making Pareto Graphs).

To tough it up, I cannot attach the file which I am working on as its company confidential. I can still provide some clues about the Columns & Rows. I have a report which has 31 Columns and 100 Rows which is updated on a weekly basis and the size is alsmost the same everytime. I would like to search the data in Column 29 and compile the above mentioned report.

If the above can be deviced I can proceed with the rest.

Thanks
Cheers,
Brijesh

 

[brijeshdeshpande]

This is a first time I have registered for any of the forums online. I have been working with Excel for quite sometime but only with the Formulae & Formatting and very little with Macros. I have achieved all my goals with the above, but this task has been a bit confusing for me and has confused me a lot and I am sure such a thing cannot be done through formulae and if it can be then i possibly have lost patience.

What I need? is a Macro for Finding words within a Dynamic List (which will be updated with new words and redundant words shall be removed on weekly basis) in a given workbook (within some specific columns on all Sheets) and compiling a report of their count of instances of appearance in all this Data. After the count is generated a Pareto Graph has to be generated on the basis of these counts (i am well versed with making Pareto Graphs).

To tough it up, I cannot attach the file which I am working on as its company confidential. I can still provide some clues about the Columns & Rows. I have a report which has 31 Columns and 100 Rows which is updated on a weekly basis and the size is alsmost the same everytime. I would like to search the data in Column 29 and compile the above mentioned report.

If the above can be deviced I can proceed with the rest.

Thanks
Cheers,
Brijesh

Hi,

Can someone help me with this and if there are any clarifications needed, please feel free to revert.

Cheers,
Brijesh

 

[krishhi]

Here is the Solution that what i understood from your query.

Make a New Column let say "AD" and Say Name as Occourances

then type this forumale there:

=countif(AC:AC,AC2)

You can make graph on the basis of result.

Is this right? Or you have another mean?

IF yes, please provide more deatails.

 

[brijeshdeshpande]

Hi Krishhi,

I understand the solution which is given by you but thats a very basic one. In this I cannot search for a list of words, lets say

CRM,
English,
Application
Printer

And so on I have a list of words which will keep in changing and it is possible that more than one word appears in the same cell.

I have used the following formula to find out the text which is within one cell, but this permits me to search for 1 word at a time,

=IF($C$7="","",IF(AF19="","",IF(ISERROR((LEN(AF19)-LEN(SUBSTITUTE(AF19,$C$7,"")))/LEN($C$7)),"Not Valid",LEN(AF19)-LEN(SUBSTITUTE(AF19,$C$7,"")))/LEN($C$7)))

this formula is applied to about 100 Rows and I can keep changing the data within the cells D19:AG118.

I have assigned one cell with validation list which contains the list of the words to be searched, when i change the word i can see how many instances of that word exist,

But my needs is to search all the words at the same time and prepare a report on a seperate sheet which gives me a bifurcation of instances found for that word on a different sheet. Also considering this list is going to change in times ahead i need to be able to update the changes in the report.

I know this can easily be done through macros and unfortunately I dont know where to start.