brijeshdeshpande
New Member
I have been working with Excel for quite some time but only with the Formulae & Formatting and very little with Macros. I have achieved all my goals with the above, but this task was a bit confusing for me.
What I am looking for?
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 too big in size. Some clues about the Columns & Rows: This report has 31 Columns and enormous Rows which is updated on a weekly basis and the size is almost the same every time. I would like to search the data in Column 29 and compile the above mentioned report.
What I have tried…!!!
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 all Rows and I can keep changing the data within the cells D19:AG118. But the task is very tedious because if a word is added then I have to update all these changes on all the sheets in the workbook which is very time-consuming and may not be very reliable due to human error. 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 separate 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 easily.
I know this can easily be done through macros and unfortunately I don’t know where to start. If the above can be devised I can proceed with the rest.
Thanks
Cheers,
Brijesh
What I am looking for?
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 too big in size. Some clues about the Columns & Rows: This report has 31 Columns and enormous Rows which is updated on a weekly basis and the size is almost the same every time. I would like to search the data in Column 29 and compile the above mentioned report.
What I have tried…!!!
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 all Rows and I can keep changing the data within the cells D19:AG118. But the task is very tedious because if a word is added then I have to update all these changes on all the sheets in the workbook which is very time-consuming and may not be very reliable due to human error. 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 separate 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 easily.
I know this can easily be done through macros and unfortunately I don’t know where to start. If the above can be devised I can proceed with the rest.
Thanks
Cheers,
Brijesh
