Counting occurences of same product ID within separate date ranges

T

tonyk22

New Member
Joined
Jul 17, 2017
Messages
5
I have data that shows me Product ID, Date appeared on report and quantity of product. I want to understand that if the product appeared on the report for X days (excluding weekends) then there was a break in the date trend (ie a weeks gap), then appeared again, how I can count the amount of times that item has appeared?

So the product may appear (repeat) on the report 10 times but the date trend has only happened once, twice or more occasions. This would show if I have a problem with a product continually being identified as "out of stock".

Item CodeCurrentDteΣ In Market BO Qty
745606/08/20181000
745607/08/2018581
745608/08/2018688
745609/08/2018641
745610/08/2018647
745613/08/2018674
745614/08/2018703
745615/08/2018730
745616/08/2018743
745617/08/2018765
745631/08/201844
745603/09/2018181
745604/09/2018265
745605/09/2018539
745606/09/2018601
745706/08/2018897
745707/08/20181266
745708/08/20182353
745709/08/20181699
745710/08/20183124
745713/08/20183523
745714/08/20183696
745715/08/20183960
745716/08/20183967
745717/08/20184436
745723/08/2018167
745729/08/2018325
745730/08/20182009
745731/08/20182089
745703/09/20182609
745704/09/20183890
745705/09/20184480
745706/09/20184507
745710/09/20184972
745711/09/20185480
745712/09/20185960

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Try this for data starting "A2" and results in columns "E & F".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG15Sep38
[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, Dt [COLOR="Navy"]As[/COLOR] Date, t
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), 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"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Array(Dn.Offset(, 1), 0)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
            [COLOR="Navy"]If[/COLOR] DateDiff("d", Q(0), Dn.Offset(, 1).Value) > 1 [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]For[/COLOR] Dt = Q(0) To Dn.Offset(, 1).Value
                    [COLOR="Navy"]If[/COLOR] Not WeekdayName(Weekday(Dt, 4), True) = "Sat" And Not WeekdayName(Weekday(Dt, 4), True) = "Sun" [COLOR="Navy"]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        t = WeekdayName(Weekday(Dt, 4), True)
                        [COLOR="Navy"]Exit[/COLOR] For
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Dt
            [COLOR="Navy"]End[/COLOR] If
        Q(0) = Dn.Offset(, 1).Value
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Range("E1:F1") = Array("Item Code", "Count")
C = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .KEYS
    C = C + 1
    Cells(C, "e") = K
    Cells(C, "F") = .Item(K)(1)
[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

Item Code
CurrentDte
Σ In Market BO Qty
7456
10/08/2018
647
7456
13/08/2018
674

<tbody>
</tbody>

<tbody>
</tbody>
MickG said:
Try this for data starting "A2" and results in columns "E & F".
Code: 
[COLOR=navy]Sub[/COLOR] MG15Sep38
[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, Dt [COLOR=navy]As[/COLOR] Date, t
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), 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]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        .Add Dn.Value, Array(Dn.Offset(, 1), 0)
    [COLOR=navy]Else[/COLOR]
        Q = .Item(Dn.Value)
            [COLOR=navy]If[/COLOR] DateDiff("d", Q(0), Dn.Offset(, 1).Value) > 1 [COLOR=navy]Then[/COLOR]
                [COLOR=navy]For[/COLOR] Dt = Q(0) To Dn.Offset(, 1).Value
                    [COLOR=navy]If[/COLOR] Not WeekdayName(Weekday(Dt, 4), True) = "Sat" And Not WeekdayName(Weekday(Dt, 4), True) = "Sun" [COLOR=navy]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        t = WeekdayName(Weekday(Dt, 4), True)
                        [COLOR=navy]Exit[/COLOR] For
                    [COLOR=navy]End[/COLOR] If
                [COLOR=navy]Next[/COLOR] Dt
            [COLOR=navy]End[/COLOR] If
        Q(0) = Dn.Offset(, 1).Value
        .Item(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]Dim[/COLOR] C [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Range("E1:F1") = Array("Item Code", "Count")
C = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .KEYS
    C = C + 1
    Cells(C, "e") = K
    Cells(C, "F") = .Item(K)(1)
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thank you for the code, I've entered into the data but it is returning too many counts. It would appear that if there is a break in entries due to weekend (last date a Friday, then next date a Monday), it's counting them as separate occurences vs just 1. As I am a novice and don't fully understand code, is there a simple fix to correct this? An example is my 1st stock code 7456, you can see the break in data entries due to the weekend but code is reading this as 2 occurences vs one.
 
Upvote 0
This is my results , what are yours ???
Item CodeCount
74563
74575
<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

It may be the "Weekday" index is incorrect
Code: 
 t = WeekdayName(Weekday(Dt, 4), True)


From the line above Check that the "WeekdayName" is correct against the date variable "Dt".
If incorrect change the value "4" until you get the correct Day against the date variable "Dt".
Then adjust the "4" (twice) value in the code , 2 lines above.
 
Upvote 0
Thanks again Mike, not sure that corrects the issue.

Item CodeCurrentDteΣ In Market BO Qty
745606/08/20181000
745607/08/2018581
745608/08/2018688
745609/08/2018641
745610/08/2018647
745613/08/2018674
745614/08/2018703
745615/08/2018730
745616/08/2018743
745617/08/2018765
745631/08/201844
745603/09/2018181
745604/09/2018265
745605/09/2018539
745606/09/2018601
<colgroup><col width="56" style="width: 42pt;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 3114;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 4053;"> <tbody> </tbody>

As you can see above, just taking the 1st product there are consecutive entries date wise from 06/08/2018 - 10/08/2018. The data then starts again from 13/08/2018. My understanding is the code is calculating the gap as a separate occurrence, however it is the same occurrence and the weekend dates are missing. How can I achieve my aim without adding date data into the source? The outcome in this example should be 2, not 3.
 
Upvote 0
I have since been shared a formula that seems to work.

=IF(A3=A2,IF(NETWORKDAYS.INTL(B2,B3)=2,0,1),1)
Thanks for helping
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Count how many times a product sold in a date range
Replies
3
Views
298
Peter_SSs
Peter_SSs
J
  • Question
Count out of date record
Replies
5
Views
622
Fluff
Fluff
K
  • Question
Extract data between two sets of dates in two columns.
Replies
4
Views
997
RasGhul
RasGhul
T
  • Question
Multiple match
Replies
5
Views
553
Marcelo Branco
M
S
  • Question
CountIf Function for Variable Date Range
Replies
9
Views
3K
dreid1011
dreid1011

Forum statistics

Threads
1,215,376
Messages
6,124,594
Members
449,174
Latest member
chandan4057

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