Counting consecutive duplicates of identical values in chaotic order

[thomas819]

Hello all,
I would like to ask you for help. I am currently facing very interesting challenge and unfortunately I can't solve it alone.

In this table you can see list of 5 products with error description.

Product	Error description
1​	error0
1​	no error
2​	error1
2​	erroe4
3​	no error
4​	error2
5​	error2
4​	no error
4​	no error
5​	no error
5​	no error
5​	no error
1​	error5
5​	no error
1​	no error
4​	error2
4​	no error
1​	no error
3​	no error
3​	no error
5​	error4
1​	no error
4​	no error
1​	no error
3​	no error
4​	error6
5​	no error
5​	no error

Result should like this:
I need to find out how many times we got "no error" without interruption in row in last occurrence of an particular product.
If we have have as last record "error" then counter should be reset.

Actual result I am looking for:

1​	no error	4x
2​	no error	0x
3​	no error	4x
4​	no error	0x
5​	no error	2x

Thank you very much for any help you may provide, because I can't move forward at all.

 

[Peter_SSs]

Does this help?

22 10 02.xlsm
	A	B	C	D	E
1	Product	Error description		Product	Count
2	1	error0		1	4
3	1	no error		2	0
4	2	error1		3	4
5	2	erroe4		4	0
6	3	no error		5	2
7	4	error2
8	5	error2
9	4	no error
10	4	no error
11	5	no error
12	5	no error
13	5	no error
14	1	error5
15	5	no error
16	1	no error
17	4	error2
18	4	no error
19	1	no error
20	3	no error
21	3	no error
22	5	error4
23	1	no error
24	4	no error
25	1	no error
26	3	no error
27	4	error6
28	5	no error
29	5	no error
Count

Cell Formulas
Range	Formula
E2:E6	E2	=LEN(TRIM(RIGHT(SUBSTITUTE(CONCAT(IF(A$2:A$29=D2,IF(B$2:B$29="no error",1," "),""))&1," ",REPT(" ",999)),999)))-1

 

[thomas819]

Does this help?

22 10 02.xlsm
	A	B	C	D	E
1	Product	Error description		Product	Count
2	1	error0		1	4
3	1	no error		2	0
4	2	error1		3	4
5	2	erroe4		4	0
6	3	no error		5	2
7	4	error2
8	5	error2
9	4	no error
10	4	no error
11	5	no error
12	5	no error
13	5	no error
14	1	error5
15	5	no error
16	1	no error
17	4	error2
18	4	no error
19	1	no error
20	3	no error
21	3	no error
22	5	error4
23	1	no error
24	4	no error
25	1	no error
26	3	no error
27	4	error6
28	5	no error
29	5	no error
Count

Cell Formulas
Range	Formula
E2:E6	E2	=LEN(TRIM(RIGHT(SUBSTITUTE(CONCAT(IF(A$2:A$29=D2,IF(B$2:B$29="no error",1," "),""))&1," ",REPT(" ",999)),999)))-1

Hi Peter,
you are magic. Thank you very much .
To be honest I never even concider LEN nor TRIM functions to solve this issue. My approach was through FREQUENCY function...

Thank you

 

[Peter_SSs]

You are very welcome. My first thought also was related to FREQUENCY but I ended up thinking that TRIM(RIGHT( ...)) was an easier way to check the 'last' results.

 

[thomas819]

You are very welcome. My first thought also was related to FREQUENCY but I ended up thinking that TRIM(RIGHT( ...)) was an easier way to check the 'last' results.

Hi Peter, I would like to ask you for other favour.
Can you somehow tweak your formula... because I have to run it using Office 2016 and CONCAT function is not supported.

At home I have Office 2019 and here is no problem running that function, but under Office 2016 is other story.

Thank you in advance

 

[Peter_SSs]

You could try this user-defined function.

Function MyCount(rData As Range, sProd As String, sDesc As String) As Long
  Dim a As Variant
  Dim i As Long
  
  a = rData.Value
  i = UBound(a)
  Do Until (a(i, 1) = sProd And LCase(a(i, 2)) <> LCase(sDesc)) Or i = 1
    If a(i, 1) = sProd And LCase(a(i, 2)) = LCase(sDesc) Then MyCount = MyCount + 1
    i = i - 1
  Loop
End Function

Note that the data range fed into the function includes the heading row.

thomas819.xlsm
	A	B	C	D	E
1	Product	Error description		Product	Count
2	1	error0		1	4
3	1	no error		2	0
4	2	error1		3	4
5	2	erroe4		4	0
6	3	no error		5	2
7	4	error2
8	5	error2
9	4	no error
10	4	no error
11	5	no error
12	5	no error
13	5	no error
14	1	error5
15	5	no error
16	1	no error
17	4	error2
18	4	no error
19	1	no error
20	3	no error
21	3	no error
22	5	error4
23	1	no error
24	4	no error
25	1	no error
26	3	no error
27	4	error6
28	5	no error
29	5	no error
Count

Cell Formulas
Range	Formula
E2:E6	E2	=MyCount(A$1:B$29,D2,"no error")

 

[thomas819]

You could try this user-defined function.

Function MyCount(rData As Range, sProd As String, sDesc As String) As Long
  Dim a As Variant
  Dim i As Long
 
  a = rData.Value
  i = UBound(a)
  Do Until (a(i, 1) = sProd And LCase(a(i, 2)) <> LCase(sDesc)) Or i = 1
    If a(i, 1) = sProd And LCase(a(i, 2)) = LCase(sDesc) Then MyCount = MyCount + 1
    i = i - 1
  Loop
End Function

Note that the data range fed into the function includes the heading row.

thomas819.xlsm
	A	B	C	D	E
1	Product	Error description		Product	Count
2	1	error0		1	4
3	1	no error		2	0
4	2	error1		3	4
5	2	erroe4		4	0
6	3	no error		5	2
7	4	error2
8	5	error2
9	4	no error
10	4	no error
11	5	no error
12	5	no error
13	5	no error
14	1	error5
15	5	no error
16	1	no error
17	4	error2
18	4	no error
19	1	no error
20	3	no error
21	3	no error
22	5	error4
23	1	no error
24	4	no error
25	1	no error
26	3	no error
27	4	error6
28	5	no error
29	5	no error
Count

Cell Formulas
Range	Formula
E2:E6	E2	=MyCount(A$1:B$29,D2,"no error")

Hi Peter,
thank you once more. You are trully excelent. I also tried this (custom function) approach, but it was running very long. I am talkingh about tens of seconds.

Thank you very much

 

[Peter_SSs]

I also tried this (custom function) approach, but it was running very long.

Sounds like you must have a lot of these formulas in the sheet then and/or very large data ranges with products well scattered?

If all the error descriptions are lower case like your samples then changing the function to the code below may help, though I'm not sure that the difference will be much.

Function MyCount(rData As Range, sProd As String, sDesc As String) As Long
  Dim a As Variant
  Dim i As Long
  
  a = rData.Value
  i = UBound(a)
  Do Until (a(i, 1) = sProd And a(i, 2) <> sDesc) Or i = 1
    If a(i, 1) = sProd Then
      If a(i, 2) = sDesc Then MyCount = MyCount + 1
    End If
    i = i - 1
  Loop
End Function

 

[thomas819]

Sounds like you must have a lot of these formulas in the sheet then and/or very large data ranges with products well scattered?

If all the error descriptions are lower case like your samples then changing the function to the code below may help, though I'm not sure that the difference will be much.

Function MyCount(rData As Range, sProd As String, sDesc As String) As Long
  Dim a As Variant
  Dim i As Long
 
  a = rData.Value
  i = UBound(a)
  Do Until (a(i, 1) = sProd And a(i, 2) <> sDesc) Or i = 1
    If a(i, 1) = sProd Then
      If a(i, 2) = sDesc Then MyCount = MyCount + 1
    End If
    i = i - 1
  Loop
End Function

Hi Peter, thank you.
But you missunderstund what I meant. I tried create my own function, but since this was my first attempt to do so, it wasn't write in sufficient way and therefore vba runs over 40s.

Your original custom function run smoothly and very fast

Thank you.

 

[Peter_SSs]

OK, thanks for the clarification. Glad it is working well for you.