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

brijeshdeshpande

New Member
Joined
Oct 26, 2010
Messages
3
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

brijeshdeshpande

New Member
Joined
Oct 26, 2010
Messages
3
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

Active Member
Joined
Sep 8, 2008
Messages
328
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

New Member
Joined
Oct 26, 2010
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,654
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top