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

[smide]

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)

 

[pgc01]

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

 

[MickG]

Try this for all "Products".
Each "Product" shown in subsequent columns.

[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]
        .Add Dn.Offset(, Ac).Value, Dn.Offset(, Ac)
    [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

 

[smide]

Rather interesting solution for ALL Products.
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 ?

 

[MickG]

ADVERTISEMENT

Try this:-

[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]
        .Add Dn.Offset(, Ac).Value, Dn.Offset(, Ac)
    [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

 

[smide]

Perfect. Thank you very much.

 

[MickG]

You're welcome