No announcement yet.

reset duplicate count & continue the count in sequential manner after resetting

  • Filter
  • Time
  • Show
Clear All
new posts

  • reset duplicate count & continue the count in sequential manner after resetting

    HI guys,

    I have been trying to find the answer to my question hoping that anyone here could help me. Any help is truly appreciated.

    In a scenario where I have to count duplicate value like the below :-

    Column G(date)------Column H---Column I (count)------Column J(weekday) ---------Column K
    Tues(25th Dec)----------Apple----- 1-------------------------Weekday 2----------------------Email sent
    Wed(26th Dec) ---------Pear ------- 1-------------------------Weekday 3-----------------------Email sent
    Thur(27th Dec) --------- Apple ------2------------------------Weekday 4 --------------------Duplicate
    Frida(28th Dec)---------Apple--------3-----------------------Weekday 5 -------------------- Duplicate

    Next Monday

    Column G(date)------Column H---Column I (count)------Column J(weekday) --------------Column K
    Mon(31st Dec)----------Apple----------- 1-------------------------Weekday 1----------------------Email sent ------------Reset count when weekday =1
    Tues(1st Jan ) -----------Pear ------------ 1-------------------------Weekday 2-----------------------Email sent-
    Wed(2nd Jan ) -------- Apple -------------2------------------------Weekday3 --------------------- - -------------continue the sequence number after reset
    Thur(3rd Jan)----------Apple---------------3-----------------------Weekday 4 ----------------------

    I already have a code that will reset count

    Sub mon()
    Dim LastR As Range, x As Long
    If IsEmpty([h5]) Then Exit Sub
    Set LastR = [i5]
    If Not IsEmpty(LastR) Then Set LastR = Range("i" & Rows.count).End(xlUp)(2)
    x = Range("h" & Rows.count).End(xlUp).Row
    If x < LastR.Row Then Exit Sub
    With Range(LastR, Range("i" & x))
    .Formula = "=mod(countif(h$" & .Row & ":h" & .Row & ",h" & .Row & "),5)+" & _
    "if(mod(countif(h$" & .Row & ":h" & .Row & ",h" & .Row & "),5)=0,5,0)"
    .Value = .Value
    End With
    End Sub
    So how do I continue the count in a sequential manner on wed as shown in the next monday ( in orange) after the reset without affecting the count in last weeks data ?
    count duplicate , reset count on monday when weekday is equal to 1 and continue the count after the reset when weekday is equal to 2,3,4,5
    Last edited by AliGW; December 28th, 2018, 17:50. Reason: Code tags correctly applied.

  • #2
    help anyone


    • #3
      I am sure someone will help, but you posted during the Christmas holidays, so many people will have been otherwise engaged!

      Enthusiastic self-taught user of MS Excel who's always learning!
      If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.


      • #4
        Hey Ali,

        Forgive me. I totally forgot that its the Christmas Holidays. Thanks for reminding me.


        • #5

          hi does anyone know how to?