Countinue numbering the duplicate list based on the last duplicate list numbering

[lyna91]

Hi everyone, thank you so much for reading this post. Any help on this is truly appreciated.

I have a list starting in column H5 and a VBA that counts the duplicate per week up to 5 and restart the count every monday and the count will be displayed in the column starting from I5. I would like to VBA to be able to continue the last duplicate number

I already have a code but it seems to be not working not sure where I went wrong.

I would like it to do something like this:

Previous Week Friday:-

Column H-----Column I (count)
5.Apple-------1
6.Pear -------1
7.Apple ------2


This week Monday after restarting the numbering:-

Column H-----Column I (count)
8.Apple-------1
9.Pear -------1
10.Apple -----2

This week tuesday
11. Apple-----3
12. Apple-----4


Right now the coding I have instead of countinue the counting in tuesday like the above. It starts to renumbering it back to 1
Below is the code:

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$5:h5,h5),5)+if(mod(countif(h$5:h5,h5),5)=0,5,0)"
.Value = .Value
End With
End Sub


Please help me . Thank you so much

 

[lyna91]

but this wont continue count from last count after restarting

 

[James006]

Hello again,

Thanks for the file ...

Just copied the formula posted earlier in cell J2 ... and copied it down ...

From what I can see ... it does produce the correct results ...

Can you test it on your side ...

And could you manually give me the actual results expected in Column I ...

Thanks

 

[lyna91]

ok doing it now

 

[lyna91]

https://ufile.io/andaz

 

[lyna91]

I hope this made it clearer so sorry. The current code works fine to restart but issue with continuing the number after restart

 

[lyna91]

ALso when the email is send there will be date stamp in column Q so that it wont resend the email to those with date stamp in column Q

 

[James006]

A picture is an inert object ... dead ... with which...you cannot do anything ...

Thanks for posting the file you have used for your tests ...

 

[lyna91]

im so sorry https://ufile.io/z2nfi.

 

[lyna91]

Allow me to appologize I'm really bad at explaining

 

[James006]

Thanks for posting your attempt 3 file ...

Let me dig into the logical structure behind your sheets ...

Will get back to you asap ...