Count Consecutive Dates

[DeaninGA]

So I have a list of products that may show up on a report for consecutive dates. I'd like to count the current consecutive Workdays (Monday -Friday) the product has been on the list and reset the counter once it disappears for 1 day. Thank you in advance. Dean

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Report Day[/TD]
[TD="align: center"]Product #[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5/1/2019[/TD]
[TD="align: center"]115112[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]5/1/2019[/TD]
[TD="align: center"]110123[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]5/1/2019[/TD]
[TD="align: center"]995000[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]5/2/2019[/TD]
[TD="align: center"]115112[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5/2/2019[/TD]
[TD="align: center"]99500[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]5/3/2019[/TD]
[TD="align: center"]115112[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]5/3/2019[/TD]
[TD="align: center"]110123[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]5/6/2019[/TD]
[TD="align: center"]995000[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]5/6/2019[/TD]
[TD="align: center"]110123[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]5/6/2019[/TD]
[TD="align: center"]995000[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]5/7/2019[/TD]
[TD="align: center"]995000[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]5/8/2019[/TD]
[TD="align: center"]115112[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]5/8/2019[/TD]
[TD="align: center"]110123[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]5/8/2019[/TD]
[TD="align: center"]995000[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]5/9/2019[/TD]
[TD="align: center"]115112[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]5/9/2019[/TD]
[TD="align: center"]110123[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]5/9/2019[/TD]
[TD="align: center"]995000[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]5/10/2019[/TD]
[TD="align: center"]115112[/TD]

</tbody>

Sheet1​

 

[BlueAure]

Are you looking for a VBA solution or a formula? It wouldn't be hard for VBA.

 

[DeaninGA]

Sorry VBA Preferably

 

[MickG]

Assuming your data start "A2" then try this for results "Count", starting in column "C" against the first of each "Product #".

[COLOR="Navy"]Sub[/COLOR] MG22May12
[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
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & 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"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, P [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
   Ac = 1
    [COLOR="Navy"]Set[/COLOR] R = .Item(K)(1)
    R.Offset(, Ac) = 1
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] .Item(K)
            [COLOR="Navy"]If[/COLOR] P.Offset(, -1) = R.Offset(, -1) + 1 [COLOR="Navy"]Then[/COLOR]
                .Item(K)(1).Offset(, Ac) = .Item(K)(1).Offset(, Ac) + 1
                [COLOR="Navy"]Set[/COLOR] R = P
            [COLOR="Navy"]ElseIf[/COLOR] P.Offset(, -1) > R.Offset(, -1) + 1 [COLOR="Navy"]Then[/COLOR]
                 Ac = Ac + 1
                 .Item(K)(1).Offset(, Ac) = 1
                    [COLOR="Navy"]Set[/COLOR] R = P
            [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]Next[/COLOR] P
 [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[DeaninGA]

@mick Thank you will try this once I get back in the office next week