Search and count the occurences of few keywords in xcel sheet

digvijay

New Member
Joined
Feb 28, 2013
Messages
6
Hi,
I am thinking of counting the occurence of few keywords in an excel sheet. My requirement is as below:

col1 col2 col3 col4
a b if c
c 1 if d
sd r4 case fd
a sd move fd

In above example i want to search for if,case and move.
Also i want to count the individual occurence for 'if' , 'case' and 'move'.

Also i would like get a pop up asking for row no from where it should start along with which colum to search.

Kindly help ASAP...:)
Thanks for helping in advance.
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi and welcome to MrExcel.

After a bit of G**gle searching, I have managed to put this together.
It's probably not the best solution, or the one you were hoping for, but, would this help?........


Excel Workbook
ABCDEFGHIJKL
1col1col2col3col4CriteriaCountColumnRowLast Row
2IFbifcIf2C25
3c1ifMoveCase1
4Caser4casefdMove1
5asdmovefd
6
Sheet8


I have highlight all occurences of the keywords by using this Conditional Formatting formula....
=OR(A2=$F$2,A2=$F$3,A2=$F$4)

In the yellow cells (I2 and J2) type in the column and row you want to count.

I hope that helps.

Ak
 
Upvote 0
Hi and welcome to MrExcel.

After a bit of G**gle searching, I have managed to put this together.
It's probably not the best solution, or the one you were hoping for, but, would this help?........


Sheet8

*ABCDEFGHIJKL
1col1col2col3col4*CriteriaCount*ColumnRowLast Row*
2IFbifc*If2*C25*
3c1ifMove*Case1*****
4Caser4casefd*Move1*****
5asdmovefd********
6************

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G2=COUNTIF(INDIRECT($I$2&$J$2):INDIRECT($I$2&$K$2),F2)
K2=COUNTA(A2:D95)/COUNTA(A2:D2)+1
G3=COUNTIF(INDIRECT($I$2&$J$2):INDIRECT($I$2&$K$2),F3)
G4=COUNTIF(INDIRECT($I$2&$J$2):INDIRECT($I$2&$K$2),F4)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

I have highlight all occurences of the keywords by using this Conditional Formatting formula....
=OR(A2=$F$2,A2=$F$3,A2=$F$4)

In the yellow cells (I2 and J2) type in the column and row you want to count.

I hope that helps.

Ak

Thanks for replying.
My requirement is similar to this with just a slight change, please see below sheet:

table namesource columnlogictarget tabletarget column
aa1if(a1=4,if(b1=null),1,0)AA1
bb1if(b1=4,
if(c1=null,iif(a1=3),1,0))
BB1
cc1case …CC1
dd1moveDD1
ee1moveEE1
ff1case …FF1
aa2if(b2=4,
if(c1=null,iif(a2=3),1,0))
GA2
bb2if(a2=4,if(b1=null),1,0)HB2
cc2case …IC2
dd2moveJD2
ee2moveKE2
ff2moveLF2

<tbody>
</tbody>

Here what i needed was to check the occurrence of 'if', 'move', 'case' in column named LOGIC.
For this instance i would have:
if=10
case=3
move=5

and total count would be 18.
Actually i needed the code to execute in this fashion to give the desired result as stated above.
Also would like to have a pop up saying from which row to start and also which particular column is to be searched.
your help is very much appreciated.
 
Upvote 0
Thanks for replying.
My requirement is similar to this with just a slight change, please see below sheet:

table namesource columnlogictarget tabletarget column
aa1if(a1=4,if(b1=null),1,0)AA1
bb1if(b1=4,
if(c1=null,iif(a1=3),1,0))
BB1
cc1case …CC1
dd1moveDD1
ee1moveEE1
ff1case …FF1
aa2if(b2=4,
if(c1=null,iif(a2=3),1,0))
GA2
bb2if(a2=4,if(b1=null),1,0)HB2
cc2case …IC2
dd2moveJD2
ee2moveKE2
ff2moveLF2

<tbody>
</tbody>


Here what i needed was to check the occurrence of 'if', 'move', 'case' in column named LOGIC.
For this instance i would have:
if=10
case=3
move=5

and total count would be 18.
Actually i needed the code to execute in this fashion to give the desired result as stated above.
Also would like to have a pop up saying from which row to start and also which particular column is to be searched.
Along with this actually i need to build a standard process so that any given excel sheet with a row no & column name would work.
your help is very much appreciated.
 
Upvote 0
Hi,

I'm sorry, but I don't write VBA code, so I cannot help you further with this.

I may be mistaken here, but it looks like you want to count how many IF formulas you have, is that correct?

Ak
 
Upvote 0
Hi,

I'm sorry, but I don't write VBA code, so I cannot help you further with this.

I may be mistaken here, but it looks like you want to count how many IF formulas you have, is that correct?

Ak




Yes exactly.
Thanks for understanding this.
Also not only if, there might be different keywords like MOVE or CASE etc.
I think you got what i mean...?
Isn't it...:)

If not you can you please guide me to one who writes VBA....

Many thanks for your support...:)
Have a nice time.
 
Upvote 0
Hi,

Take a look here....
http://www.mrexcel.com/forum/excel-...ber-cells-formula-references.html#post3401564

There is information (VBA code) that will help you regarding displaying cell formulas.
Once you have displayed the cell formulas, you should be able to do a COUNTIF using one or more wildcards.

=COUNTIF($A$2:$A$5,"*"&B2&"*")

You would have to change the range $A$2:$A$5 to suit yours and change B2 to the cell reference that will hold If, Case or Move.

I hope this helps.

Good luck.

Ak
 
Upvote 0
Thanks for your concern.
But i am not aware how this works up.
Can you please let me have a sample code for below example:


table namesource columnlogictarget tabletarget column
aa1if(a1=4,if(b1=null),1,0)AA1
bb1if(b1=4,
if(c1=null,iif(a1=3),1,0))
BB1
cc1case …CC1
dd1moveDD1
ee1moveEE1
ff1case …FF1
aa2if(b2=4,
if(c1=null,iif(a2=3),1,0))
GA2
bb2if(a2=4,if(b1=null),1,0)HB2
cc2case …IC2
dd2moveJD2
ee2moveKE2
ff2moveLF2

<TBODY>
</TBODY>



Many thanks for helping... :)
 
Upvote 0
Hi,

In the column Logic, are these formulas?
Eg. if(a1=4,if(b1=null),1,0) should this be =if(a1=4,if(b1="null",1,0))

Ak
 
Upvote 0
Hi,

I'm going to assume that the column Logic contains some sort of IF formula.

Step1 Open VBA
Press Alt F11
Click on Insert, click Module.
Paste this into the white area....

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

Press Alt F11 to return to your worksheet.

You will need to add a "Helper" column (F) to display the formulas used in column Logic.
This column (F) must have the following formula added to it.....

=GetFormula(C2)

Where C2 is the first cell in column Logic with a formula.
Copy this formula down until the last cell required.

You will now need to type IF, case, move into seperate cells so that you can do a count on them, please see the following table as an example...

Excel Workbook
ABCDEFGHIJKL
1table namesource colunLogictarget tabletarget columnCell FormulaCriteriaCount1Count2Count3
2aa1FALSEAA1=IF(A1=4,IF(B1="null",1,0))IF101010
3bb10=IF(B1=4,1,0)case333
4BB1FALSE=IF(C1="null",IF(A1=3,1,0))move555
5cc1caseCC1case
6dd1moveDD1move
7ee1moveEE1move
8ff1caseFF1case
9aa20=IF(B2=4,1,0)
10GA2FALSE=IF(C1="null",IF(A2=3,1,0))
11bb2FALSEHB2=IF(A2=4,IF(B1="null",1,0))
12cc2caseIC2case
13dd2moveJD2move
14ee2moveKE2move
15ff2moveLF2move
16
Sheet2


The formula in F2 needs to be copied down.
The formula in I2 needs to be copied down.
The formula in J2 needs to be entered with ctrl shift enter and NOT just enter.
The formula in K2 needs to be copied down.

I have used different formulas to show how this can be resolved.
The main difference here is that K2 will display the correct result regardless of the use of UPPER or LOWER case letters, see the following....

Excel Workbook
ABCDEFGHIJKL
1table namesource colunLogictarget tabletarget columnCell FormulaCriteriaCount1Count2Count3
2aa1FALSEAA1=IF(A1=4,IF(B1="null",1,0))IF101010
3bb10=IF(B1=4,1,0)case223
4BB1FALSE=IF(C1="null",IF(A1=3,1,0))move445
5cc1CASECC1CASE
6dd1moveDD1move
7ee1MoveEE1Move
8ff1caseFF1case
9aa20=IF(B2=4,1,0)
10GA2FALSE=IF(C1="null",IF(A2=3,1,0))
11bb2FALSEHB2=IF(A2=4,IF(B1="null",1,0))
12cc2caseIC2case
13dd2moveJD2move
14ee2moveKE2move
15ff2moveLF2move
16
Sheet2


IF, column Logic does contain formulas, then this shouldn't be an issue for you and you should be able to use the formula in I2.

I got the above solutions by doing a quick G**gle search........

Count1 Excel Count the number of times a word appears in a range?
Count2 Formulas to count the occurrences of text, characters, or words in Excel for Mac
Count3 Count number of times a string exist in multiple cells using excel formula | Get Digital Help - Microsoft Excel resource

I hope this resolves the problem for you, if not then I am sorry but I cannot help you further with this and I suggest that you start a new question.
In your new question you may want to include a more accurate example of your data and the actual formulas used.

Good luck.

Ak
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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