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]

Thank you so much James and apology for the repeated bad attempt to explain it to you. You have no idea how grateful I am

 

[James006]

Hello again,

Let's exchange a few words in plain English ... (and forget the macros for a while)

- How and When do you use this file ?

- Your follow-up for your invoices ( in any given week ) can cover which periods ?

 

[lyna91]

Hello again,

Let's exchange a few words in plain English ... (and forget the macros for a while)

- How and When do you use this file ?

- Your follow-up for your invoices ( in any given week ) can cover which periods ?

HI james. The dates are all irrelevant, data will be updated everyday in that file and will count duplicates from 1 to 5 in a week . the count will be reset on every monday (1st weekday). Every count 1 email will be triggered. as shown in the reminder macro tab

When reset on monday this is the coding:-

Sub restart() 'TO restart on monday
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 the issue now is how to make it continue the count from the last column
because right now it keeps on restarting the count.


Sub Count() 'To count as usual and continue count after restarting but not working

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



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. I hope this explains it? Im so sorry I know this very confusing as this is very unique I have
tried googling the answers for awhile now and to no avail.

 

[James006]

Can you test following formula :

=COUNTIF(INDIRECT("H"&((INT(INT(ROW()-5)/6)+1)*5)+INT(INT(ROW()-5)/6)):H5,H5)

 

[lyna91]

Can you test following formula :

=COUNTIF(INDIRECT("H"&((INT(INT(ROW()-5)/6)+1)*5)+INT(INT(ROW()-5)/6)):H5,H5)

Hi James it follow target value but how did it read what weekday it is. Is there anywhere for me to integrate it into the code?

 

[James006]

Hi James it follow target value but how did it read what weekday it is. Is there anywhere for me to integrate it into the code?

It does not follow the WeekDay ... since ... when I asked you to explain in plain English ... you have not explained it ...

Do not hesitate to explain how the Weekday modifies the span of the range ....

 

[lyna91]

hahaha Im sorry there is already a formula that calculates the weekday in sheet2 column B9
the aim is to when weekday is equal to one ill use
this code:-


Sub restart() 'TO restart on monday
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

To restart the count.

So the missing code right now is when weekday is equal to 2,3,4 and 5
it will continue count from the last value of the duplicate data. Does this makes sense?

To make it simpler below is an example:-

Friday(weekday 5) last week data input:-

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


This week Monday (weekday 1)after restarting the numbering:-

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

This week tuesday (weekday 2)
11. Apple-----3
12. Apple-----4

 

[lyna91]

Sub restart() 'TO restart on monday --> manually activate this code on monday

 

[James006]

Sorry ...

Your explanations are not clear at all ...

The main question you have to answer to :

How the Weekday modifies the Countif range ...???

 

[lyna91]

Weekday 1 restart duplicate count.
Weekday 2,3,4,5 count duplicate as usual. Is that what you were asking? Im really sorry english is not my first language and im really new with vba.