Search and count the occurences of few keywords in xcel sheet

[digvijay]

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.

 

[Akashwani]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	col1	col2	col3	col4		Criteria	Count		Column	Row	Last Row
2	IF	b	if	c		If	2		C	2	5
3	c	1	if	Move		Case	1
4	Case	r4	case	fd		Move	1
5	a	sd	move	fd
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]

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

*

A

B

C

D

E

F

G

H

I

J

K

L

*

*

*

b

c

*

If

*

*

c

*

Case

*

*

*

*

*

r4

fd

*

Move

*

*

*

*

*

a

sd

fd

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]col1[/TD]
[TD="align: center"]col2[/TD]
[TD="align: center"]col3[/TD]
[TD="align: center"]col4[/TD]

[TD="align: center"]Criteria[/TD]
[TD="align: center"]Count[/TD]

[TD="align: center"]Column[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"]Last Row[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #00ccff"]IF[/TD]

[TD="bgcolor: #00ccff"]if[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #ffff00"]C[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #00ccff"]if[/TD]
[TD="bgcolor: #00ccff"]Move[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #00ccff"]Case[/TD]

[TD="bgcolor: #00ccff"]case[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #00ccff"]move[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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="width: 561"]
<tbody>[TR]
[TD]table name[/TD]
[TD]source column[/TD]
[TD]logic[/TD]
[TD]target table[/TD]
[TD]target column[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a1[/TD]
[TD]if(a1=4,if(b1=null),1,0)[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b1[/TD]
[TD]if(b1=4,
if(c1=null,iif(a1=3),1,0))[/TD]
[TD]B[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c1[/TD]
[TD]case …[/TD]
[TD]C[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d1[/TD]
[TD]move[/TD]
[TD]D[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e1[/TD]
[TD]move[/TD]
[TD]E[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]f1[/TD]
[TD]case …[/TD]
[TD]F[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a2[/TD]
[TD]if(b2=4,
if(c1=null,iif(a2=3),1,0))[/TD]
[TD]G[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b2[/TD]
[TD]if(a2=4,if(b1=null),1,0)[/TD]
[TD]H[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c2[/TD]
[TD]case …[/TD]
[TD]I[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d2[/TD]
[TD]move[/TD]
[TD]J[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e2[/TD]
[TD]move[/TD]
[TD]K[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]f2[/TD]
[TD]move[/TD]
[TD]L[/TD]
[TD]F2[/TD]
[/TR]
</tbody>[/TABLE]

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]

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

[TABLE="class: cms_table, width: 561"]
<tbody>[TR]
[TD]table name[/TD]
[TD]source column[/TD]
[TD]logic[/TD]
[TD]target table[/TD]
[TD]target column[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a1[/TD]
[TD]if(a1=4,if(b1=null),1,0)[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b1[/TD]
[TD]if(b1=4,
if(c1=null,iif(a1=3),1,0))[/TD]
[TD]B[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c1[/TD]
[TD]case …[/TD]
[TD]C[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d1[/TD]
[TD]move[/TD]
[TD]D[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e1[/TD]
[TD]move[/TD]
[TD]E[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]f1[/TD]
[TD]case …[/TD]
[TD]F[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a2[/TD]
[TD]if(b2=4,
if(c1=null,iif(a2=3),1,0))[/TD]
[TD]G[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b2[/TD]
[TD]if(a2=4,if(b1=null),1,0)[/TD]
[TD]H[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c2[/TD]
[TD]case …[/TD]
[TD]I[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d2[/TD]
[TD]move[/TD]
[TD]J[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e2[/TD]
[TD]move[/TD]
[TD]K[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]f2[/TD]
[TD]move[/TD]
[TD]L[/TD]
[TD]F2[/TD]
[/TR]
</tbody>[/TABLE]


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]

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]

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]

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

 

[digvijay]

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="class: cms_table_cms_table"]
<TBODY>[TR]
[TD]table name[/TD]
[TD]source column[/TD]
[TD]logic[/TD]
[TD]target table[/TD]
[TD]target column[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a1[/TD]
[TD]if(a1=4,if(b1=null),1,0)[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b1[/TD]
[TD]if(b1=4,
if(c1=null,iif(a1=3),1,0))[/TD]
[TD]B[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c1[/TD]
[TD]case …[/TD]
[TD]C[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d1[/TD]
[TD]move[/TD]
[TD]D[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e1[/TD]
[TD]move[/TD]
[TD]E[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]f1[/TD]
[TD]case …[/TD]
[TD]F[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a2[/TD]
[TD]if(b2=4,
if(c1=null,iif(a2=3),1,0))[/TD]
[TD]G[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b2[/TD]
[TD]if(a2=4,if(b1=null),1,0)[/TD]
[TD]H[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c2[/TD]
[TD]case …[/TD]
[TD]I[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d2[/TD]
[TD]move[/TD]
[TD]J[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e2[/TD]
[TD]move[/TD]
[TD]K[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]f2[/TD]
[TD]move[/TD]
[TD]L[/TD]
[TD]F2[/TD]
[/TR]
</TBODY>[/TABLE]



Many thanks for helping...

 

[Akashwani]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	table name	source colun	Logic	target table	target column	Cell Formula		Criteria	Count1	Count2	Count3
2	a	a1	FALSE	A	A1	=IF(A1=4,IF(B1="null",1,0))		IF	10	10	10
3	b	b1	0			=IF(B1=4,1,0)		case	3	3	3
4	B	B1	FALSE			=IF(C1="null",IF(A1=3,1,0))		move	5	5	5
5	c	c1	case	C	C1	case
6	d	d1	move	D	D1	move
7	e	e1	move	E	E1	move
8	f	f1	case	F	F1	case
9	a	a2	0			=IF(B2=4,1,0)
10	G	A2	FALSE			=IF(C1="null",IF(A2=3,1,0))
11	b	b2	FALSE	H	B2	=IF(A2=4,IF(B1="null",1,0))
12	c	c2	case	I	C2	case
13	d	d2	move	J	D2	move
14	e	e2	move	K	E2	move
15	f	f2	move	L	F2	move
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
	A	B	C	D	E	F	G	H	I	J	K	L
1	table name	source colun	Logic	target table	target column	Cell Formula		Criteria	Count1	Count2	Count3
2	a	a1	FALSE	A	A1	=IF(A1=4,IF(B1="null",1,0))		IF	10	10	10
3	b	b1	0			=IF(B1=4,1,0)		case	2	2	3
4	B	B1	FALSE			=IF(C1="null",IF(A1=3,1,0))		move	4	4	5
5	c	c1	CASE	C	C1	CASE
6	d	d1	move	D	D1	move
7	e	e1	Move	E	E1	Move
8	f	f1	case	F	F1	case
9	a	a2	0			=IF(B2=4,1,0)
10	G	A2	FALSE			=IF(C1="null",IF(A2=3,1,0))
11	b	b2	FALSE	H	B2	=IF(A2=4,IF(B1="null",1,0))
12	c	c2	case	I	C2	case
13	d	d2	move	J	D2	move
14	e	e2	move	K	E2	move
15	f	f2	move	L	F2	move
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