Find and count

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,</SPAN></SPAN>

Here is an example for rows 3 that will be applied to all data are in C:K
</SPAN></SPAN>
For example if 2 consecutive cells has cell M1 values, then find 3rd if it is 5, 9 or M as per M1:O1 and count them (C3&D3=M | 5 so no match 5 | 5, Go To D3&E3= 5 | 5 Check F3=5 (so after 5 | 5 found 5 count 1 result in M2), Go To E3&F3= 5 | 5 Check G3=9 (so after 5 | 5 found 9 count 1 result in N2), DO COUNT ALL SAME WAY...
</SPAN></SPAN>

Note: for now I am using formula below but I want could this formula be shorter or any VBA solution (because in this example I have used 9 numbers, in fact I have 18 numbers so in the excel 2000 does not accept too long formula)
</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNO
15 | 5
2n1n2n3n4n5n6n7n8n959M
3M55595959110
4MM95MM559010
5M55595959110
6955M95M55001
75M5M55955010
8M9MM9M559010
9M55955595120
105MM95M555100
115559MMM5M110
125MM55555M301
13M5M959MMM000
1455M595M55001
1555555M555401
16M55MM5555201
17555595MM5210
185559955M5111
1995M55M555101
20555559559320
Sheet 9
Cell Formulas
RangeFormula
M3=IF($C3&" | "&$D3=$M$1,IF($E3=M$2,1,0),0)+IF($D3&" | "&$E3=$M$1,IF($F3=M$2,1,0),0)+IF($E3&" | "&$F3=$M$1,IF($G3=M$2,1,0),0)+IF($F3&" | "&$G3=$M$1,IF($H3=M$2,1,0),0)+IF($G3&" | "&$H3=$M$1,IF($I3=M$2,1,0),0)+IF($H3&" | "&$I3=$M$1,IF($J3=M$2,1,0),0)+IF($I3&" | "&$J3=$M$1,IF($K3=M$2,1,0),0)
N3=IF($C3&" | "&$D3=$M$1,IF($E3=N$2,1,0),0)+IF($D3&" | "&$E3=$M$1,IF($F3=N$2,1,0),0)+IF($E3&" | "&$F3=$M$1,IF($G3=N$2,1,0),0)+IF($F3&" | "&$G3=$M$1,IF($H3=N$2,1,0),0)+IF($G3&" | "&$H3=$M$1,IF($I3=N$2,1,0),0)+IF($H3&" | "&$I3=$M$1,IF($J3=N$2,1,0),0)+IF($I3&" | "&$J3=$M$1,IF($K3=N$2,1,0),0)
O3=IF($C3&" | "&$D3=$M$1,IF($E3=O$2,1,0),0)+IF($D3&" | "&$E3=$M$1,IF($F3=O$2,1,0),0)+IF($E3&" | "&$F3=$M$1,IF($G3=O$2,1,0),0)+IF($F3&" | "&$G3=$M$1,IF($H3=O$2,1,0),0)+IF($G3&" | "&$H3=$M$1,IF($I3=O$2,1,0),0)+IF($H3&" | "&$I3=$M$1,IF($J3=O$2,1,0),0)+IF($I3&" | "&$J3=$M$1,IF($K3=O$2,1,0),0)


Thanks In Advance
</SPAN></SPAN>
Using version 2000
</SPAN></SPAN>

Regards,
</SPAN>
Moti
</SPAN>
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
G'day Moti,

I have found an answer for the 559 and the 55M conditions, because they can only occur in one way, however this answer does not handle 5555 being counted twice as (555)5 and 5(555)

The answer comes from here
http://www.cpearson.com/Excel/stringformulas.aspx

In the first instance write the full required string into N1 (559) & O1 (55M)

then concatenate C-K in cell L3
=CONCATENATE(C3,D3,E3,F3,G3,H3,I3,J3,K3)

then apply the following to the result of the 559 column and copy across to the 55M column

=IF(LEN($N$1)=0,0,(LEN($L3)-LEN(SUBSTITUTE(UPPER($L3),UPPER($N$1),"")))/LEN($N$1))

Note: the Substitute function makes it non-case sensitive, i.e will work for both 55m and 55M and count them as the same thing
 
Last edited:
Upvote 0
Try this:

Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090492a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1090492-find-count.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
va = Range([COLOR=brown]"C3:K"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]3[/COLOR])
z = Replace(Range([COLOR=brown]"M1"[/COLOR]), [COLOR=brown]" | "[/COLOR], [COLOR=brown]""[/COLOR])
k = [COLOR=crimson]2[/COLOR]
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    d([COLOR=crimson]5[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=crimson]9[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=brown]"M"[/COLOR]) = [COLOR=crimson]0[/COLOR]
        [COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR]) - [COLOR=crimson]2[/COLOR]
        [COLOR=Royalblue]If[/COLOR] (va(i, j)) & (va(i, j + [COLOR=crimson]1[/COLOR])) = z [COLOR=Royalblue]Then[/COLOR] d(va(i, j + [COLOR=crimson]2[/COLOR])) = d(va(i, j + [COLOR=crimson]2[/COLOR])) + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
        
        k = k + [COLOR=crimson]1[/COLOR]
        Range([COLOR=brown]"M"[/COLOR] & k).Resize(, [COLOR=crimson]3[/COLOR]) = Application.Transpose(Application.Transpose(Array(d.items)))
            
    [COLOR=Royalblue]Next[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]
 
Upvote 0
Maybe...

Formula in M3 copied across until O3 and down
=SUMPRODUCT(--($C3:$I3=--LEFT($M$1)),--($D3:$J3=--RIGHT($M$1)),--($E3:$K3=M$2))

Hope this helps

M.
 
Upvote 0
A better solution - works if M1 contains, for example, M|5

M3 copied across and down
=SUMPRODUCT(--($C3:$I3&""=LEFT($M$1)),--($D3:$J3&""=RIGHT($M$1)),--($E3:$K3=M$2))

M.
 
Upvote 0
G'day Moti,

I have found an answer for the 559 and the 55M conditions, because they can only occur in one way, however this answer does not handle 5555 being counted twice as (555)5 and 5(555)

The answer comes from here
http://www.cpearson.com/Excel/stringformulas.aspx

In the first instance write the full required string into N1 (559) & O1 (55M)

then concatenate C-K in cell L3
=CONCATENATE(C3,D3,E3,F3,G3,H3,I3,J3,K3)

then apply the following to the result of the 559 column and copy across to the 55M column

=IF(LEN($N$1)=0,0,(LEN($L3)-LEN(SUBSTITUTE(UPPER($L3),UPPER($N$1),"")))/LEN($N$1))

Note: the Substitute function makes it non-case sensitive, i.e will work for both 55m and 55M and count them as the same thing

DSCfromCFA, Thank you for your attempt
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 
Upvote 0
Try this:

Code:
[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1090492a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1090492-find-count.html[/COLOR][/I]

[COLOR=royalblue]Dim[/COLOR] i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], k [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va
[COLOR=royalblue]Dim[/COLOR] d [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Object[/COLOR]
Application.ScreenUpdating = [COLOR=royalblue]False[/COLOR]
[COLOR=royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
va = Range([COLOR=brown]"C3:K"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=royalblue]End[/COLOR](xlUp).Row)
[COLOR=royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]3[/COLOR])
z = Replace(Range([COLOR=brown]"M1"[/COLOR]), [COLOR=brown]" | "[/COLOR], [COLOR=brown]""[/COLOR])
k = [COLOR=crimson]2[/COLOR]
    [COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    d([COLOR=crimson]5[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=crimson]9[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=brown]"M"[/COLOR]) = [COLOR=crimson]0[/COLOR]
        [COLOR=royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR]) - [COLOR=crimson]2[/COLOR]
        [COLOR=royalblue]If[/COLOR] (va(i, j)) & (va(i, j + [COLOR=crimson]1[/COLOR])) = z [COLOR=royalblue]Then[/COLOR] d(va(i, j + [COLOR=crimson]2[/COLOR])) = d(va(i, j + [COLOR=crimson]2[/COLOR])) + [COLOR=crimson]1[/COLOR]
        [COLOR=royalblue]Next[/COLOR]
        
        k = k + [COLOR=crimson]1[/COLOR]
        Range([COLOR=brown]"M"[/COLOR] & k).Resize(, [COLOR=crimson]3[/COLOR]) = Application.Transpose(Application.Transpose(Array(d.items)))
            
    [COLOR=royalblue]Next[/COLOR]
Application.ScreenUpdating = [COLOR=royalblue]True[/COLOR]
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR]
[/FONT]
Akuini, that is amazing adapted for the range I required, I like the technique of your coding it is very undemanding and easy to modify amazing!</SPAN></SPAN>

I appreciate your help! Have a nice day
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti:biggrin:
</SPAN></SPAN>
 
Upvote 0
A better solution - works if M1 contains, for example, M|5

M3 copied across and down
=SUMPRODUCT(--($C3:$I3&""=LEFT($M$1)),--($D3:$J3&""=RIGHT($M$1)),--($E3:$K3=M$2))

M.
Marcelo Branco, I did not thought that the formula could be so tiny! I like the formula it works like a coding remarkable! </SPAN></SPAN>

I appreciate your help! Have a nice day
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti :biggrin:
</SPAN></SPAN>

 
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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