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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top