How to loop through cell to increment value on a different cell through VBA

[lgovindan]

Hi
I am trying to create a VBA script.

I have Customer Number on Column A, Date on Column B and Seq on Column C.

I start with Seq 1 for the first customer and date. If the next row has the same customer and the same date, I put seq 1 on column 1.
On change of any date for the same customer, increment seq by 1.
If the customer changes, reset seq to 1 and now loop through the on change of date increment the seq for that customer number
Repeat this until the end of the worksheet.

Visual
Customer - Date - Seq
10 Apr-4-2016 1
10 Apr-4-2016 1
10 Apr-5-2016 2
10 Apr-6-2016 3
10 Apr-6-2016 3
10 Apr-6-2016 3
10 Apr-6-2016 3
10 Apr-7-2016 4
15 Jan-1-2017 1
15 Feb-8-2017 2

So on and so forth.



I have 225000 records and am trying to automate this to get it done.

Can you please provide me a VBA script that will do this.

 

[jxb]

Have tried a formula? Something like
In cell c2
If and(a1=A2,b1=b2),max($c$1:c2)+1,1)

 

[Snakehips]

Not sure that the above will work but, =IF(A2<>A1,1,IF(B2=B1,C1,C1+1)) should.

Or incorporated into a vba code....

Sub Igovindan()
Application.ScreenUpdating = False
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("C2:C" & lastRow)

myRange.Formula = "=IF(A2<>A1,1,IF(B2=B1,C1,C1+1))"
myRange.Value = myRange.Value
Application.ScreenUpdating = False

End Sub

Hope that helps

 

[jxb]

Probably haven’t actually tried hence being poorly formatted
Was thinking loud as sometime vba is not necessary
Still do we need an AND somewhere as one needs to have customers and date in 2 consecutive rows to be the same to increment the previous highest value by 1

 

[Snakehips]

@Igovindan there is a careless typo in the last line of the code above.
Please try the code below.

Sub Igovindan()
Application.ScreenUpdating = False
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("C2:C" & lastRow)

myRange.Formula = "=IF(A2<>A1,1,IF(B2=B1,C1,C1+1))"
myRange.Value = myRange.Value
Application.ScreenUpdating = True

End Sub

Still do we need an AND somewhere as one needs to have customers and date in 2 consecutive rows to be the same to increment the previous highest value by 1

@jxb No I don't believe we do
The test in the first IF is 'is it a new Customer ID, ie different to the one above.
If True then re-set the sequence number to 1
If False, then we know that the Customer is the same as above. So we use the test of the second IF to see if the date is the same.
If true then, both Customer AND date are the same so duplicate the previous sequence number
If False then is same Customer but a different date, so increment sequence by 1

 

[lgovindan]

Have tried a formula? Something like
In cell c2
If and(a1=A2,b1=b2),max($c$1:c2)+1,1)

Hi
I have over 225000 lines. If I am doing it manually, I will check

if the customer in the previous row = customer number in current row and date in the previous row = date in current row then seq number = the same as prev row seq number. If the date is different then seq number = seq number from prev row + 1.

If customer in the previous row <> customer number in current row, then seq number = 1, irrespective of the date. This is the outer logic.

I tried your formula and it is throwing some sort of an error.

 

[lgovindan]

@Igovindan there is a careless typo in the last line of the code above.
Please try the code below.

Sub Igovindan()
Application.ScreenUpdating = False
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("C2:C" & lastRow)

myRange.Formula = "=IF(A2<>A1,1,IF(B2=B1,C1,C1+1))"
myRange.Value = myRange.Value
Application.ScreenUpdating = True

End Sub

@jxb No I don't believe we do
The test in the first IF is 'is it a new Customer ID, ie different to the one above.
If True then re-set the sequence number to 1
If False, then we know that the Customer is the same as above. So we use the test of the second IF to see if the date is the same.
If true then, both Customer AND date are the same so duplicate the previous sequence number
If False then is same Customer but a different date, so increment sequence by 1

@Snakehips - The worksheet has multiple other columns as well, i am only looking at column B and Column C (Customer number, date in the format of "YYYYMMDD") and adding the sequence number in Column D. I will copy your code and give it a try and see what happens. I will post it here my findings. Thank you for showing this.

 

[lgovindan]

@Snakehips - Thank you so much for your quick turnaround on this VBA Script. It worked.

thank you so much for all your help. Have a great day, stay safe, healthy and socially distanced...

Cheers
Laxmi

 

[Snakehips]

@Igovindan Are you saying Cust, Date, Sequence in B,C,D ?? Not A,B,C as I took it to be from your original post?
If so, the code will need tweaking.!! Do test on a backed up workbook!

EDIT: Ignore, the above as I see that it is all good!
You are most welcome. You stay safe also.

 

[lgovindan]

@Snakehips - Yes, the customer number is in cell B, the date is in Cell C and the sequence has to go to D. So I did copy the sheet to another sheet, removed the Column A, ran your script, then did a cut and paste special to remove the macro, and get it to the next sheet I wanted. I looked at it - the first customer was 536 lines and multiple different dates inside. It sequenced correctly. I then checked the next customer and it started 1 and it went on with its business. so it does work.

Thank you very much for your help. I sincerely appreciate it.
Cheers
Laxmi