Count consecutive missing values between cells based on identical text in columns

smide

Board Regular
Hello.

In columns A and B (A3:B802) I have a list of 25 different products (Product 1 - Product 25) with their corresponding 'markers' in columns C and D (when Product is in column A his 'marker' is in the same row in column C, and when Product is in column B his 'marker' is in the same row but now in column D).
Each Product's marker can have a value of zero, one or three (0,1,3).

What I need to find is to COUNT how many consecutive times each Product appears without marker 3 relative to last Product's appearance WITH marker 3 (eg. previous Product with marker 3, Product in row with lower number).

Data in row 3 are those from which 'counting' starts.

example. (for simplicity here only for Product 1)
Results are in column E when the relevant Product is in column A.
Results are in column F when the relevant Product is in column B.

So, always look at previous cells when counting missing markers (lower than 3).

 A B C D E F 1 2 Marker 1 Marker 2 Results A Results B 3 Product 1 Product 5 1 4 Product 4 Product 2 5 Product 3 Product 1 3 1 6 Product 2 Product 1 1 0 7 Product 8 Product 6 8 Product 1 Product 2 0 1 9 Product 3 Product 1 3 2 10 Product 2 Product 5 11 .... .... 12 .... ....

<tbody>
</tbody>
Results: F5 = 1, because previous Product 1 (in cell A3) has marker (cell C3) with number 1 (less than 3) and result in F5 is 1 (if C3 = 3 than F5 = 0)

F6 = 0, because previous Product 1 (in cell B5) has marker which ALREADY contains number equal to 3 (cell D5)

E8 = 1, there is a ONE previous Product 1 (cell B6) with marker lower than 3 before counting 'hits' those Product 1 with marker equal to 3 (Product 1 in cell B5 with his marker in cell D5 = 3)

F9 = 2, similar story as for E8 but now there are TWO previous Product 1 cells with markers lower than 3 before counting 'hits' those with marker equal to 3 (row 5)

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi

Try in E2:

=IF(A2<>"Product 1","",LOOKUP(10^7,FREQUENCY(IF(\$A\$1:\$B1="Product 1",IF(\$C\$1:\$D1<>3,ROW(\$A\$1:\$B1))),IF(\$A\$1:\$B1="Product 1",IF(\$C\$1:\$D1=3,ROW(\$A\$1:\$B1))))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Copy down and across in E:F

Try this for all "Products".
Each "Product" shown in subsequent columns.
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG31Dec19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] p [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]For[/COLOR] Ac = 0 To 1
[COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac + 2) <> "" [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]Set[/COLOR] .Item(Dn.Offset(, Ac).Value) = _
Union(.Item(Dn.Offset(, Ac).Value), Dn.Offset(, Ac))
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR]
col = 2
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
Num = -1: col = col + 2
Cells(2, col + 1).Resize(, 2).Value = K
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] .Item(K)
[COLOR="Navy"]If[/COLOR] p.Offset(, 2).Value = 3 [COLOR="Navy"]Then[/COLOR]
Num = Num + 1
p.Offset(, col) = Num
Num = -1
[COLOR="Navy"]Else[/COLOR]
Num = Num + 1
p.Offset(, col) = Num
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Thank you.

p.s.
The result table (after macro run) is a pretty wide, any idea how to restrict output (results) only on columns E and F ?

Try this:-
Code:
``````[COLOR=navy]Sub[/COLOR] MG31Dec04
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Num [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR][COLOR=navy],[/COLOR] p [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]For[/COLOR] Ac = 0 To 1
[COLOR=navy]If[/COLOR] Dn.Offset(, Ac + 2) <> "" [COLOR=navy]Then[/COLOR]
[COLOR=navy]If[/COLOR] Not .Exists(Dn.Offset(, Ac).Value) [COLOR=navy]Then[/COLOR]
[COLOR=navy]Else[/COLOR]
[COLOR=navy]Set[/COLOR] .Item(Dn.Offset(, Ac).Value) = _
Union(.Item(Dn.Offset(, Ac).Value), Dn.Offset(, Ac))
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
Num = -1
Cells(2, 5).Resize(, 2).Value = "Prod_Count"
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] p [COLOR=navy]In[/COLOR] .Item(K)
[COLOR=navy]If[/COLOR] p.Offset(, 2).Value = 3 [COLOR=navy]Then[/COLOR]
Num = Num + 1
p.Offset(, 4) = Num
Num = -1
[COLOR=navy]Else[/COLOR]
Num = Num + 1
p.Offset(, 4) = Num
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] p
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]``````
Regards Mick

Last edited:
Perfect. Thank you very much.

You're welcome

Replies
1
Views
102
Replies
5
Views
170
Replies
3
Views
276
Replies
4
Views
318
Replies
8
Views
432

1,196,297
Messages
6,014,525
Members
441,826
Latest member
roudarreza

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.

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

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