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:

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
2IFbifcIf C2
3c1ifMoveCase
4Caser4casefdMove
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
 

digvijay

New Member
Joined
Feb 28, 2013
Messages
6
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.
 

digvijay

New Member
Joined
Feb 28, 2013
Messages
6
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.
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

digvijay

New Member
Joined
Feb 28, 2013
Messages
6
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.
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

Take a look here....
http://www.mrexcel.com/forum/excel-questions/687380-count-number-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
 

digvijay

New Member
Joined
Feb 28, 2013
Messages
6
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... :)
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
2aa1 AA1 IF
3bb1 =IF(B1=4,1,0)case333
4BB1 =IF(C1="null",IF(A1=3,1,0))move555
5cc1caseCC1case
6dd1moveDD1move
7ee1moveEE1move
8ff1caseFF1case
9aa2 =IF(B2=4,1,0)
10GA2 =IF(C1="null",IF(A2=3,1,0))
11bb2 HB2=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
2aa1 AA1 IF
3bb1 =IF(B1=4,1,0)case223
4BB1 =IF(C1="null",IF(A1=3,1,0))move445
5cc1CASECC1CASE
6dd1moveDD1move
7ee1MoveEE1Move
8ff1caseFF1case
9aa2 =IF(B2=4,1,0)
10GA2 =IF(C1="null",IF(A2=3,1,0))
11bb2 HB2=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
 

Forum statistics

Threads
1,077,674
Messages
5,335,605
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top