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

smide

smide

Board Regular
Joined
Dec 20, 2015
Messages
162
Office Version
  1. 2016
Platform
  1. Windows
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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
 
Upvote 0
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]
        .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
 
Upvote 0
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 ?
 
Upvote 0
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]
        .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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Can Solver Save My Sanity?
Replies
2
Views
235
mickyd67
M
K
  • Question
Array SUM or SUMPRODUCT function error
Replies
1
Views
223
KratosZG
K
E
  • Solved
Duplicate Info From The Cell Above based on true / false
Replies
2
Views
165
ellison
E
B
  • Solved
Counting as one when one unique customer purchase item with multiple time in a month
Replies
1
Views
138
Eric W
Eric W
A
  • Question
Ignoring values on a different sheet
Replies
3
Views
275
Joe4
Joe4

Forum statistics

Threads
1,214,391
Messages
6,119,239
Members
448,879
Latest member
VanGirl

Share this page

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