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

lgovindan

New Member
Joined
Apr 4, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

jxb

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
  1. 2010
Platform
  1. Windows
Have tried a formula? Something like
In cell c2
If and(a1=A2,b1=b2),max($c$1:c2)+1,1)
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
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....
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

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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 = 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

New Member
Joined
Apr 4, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 4, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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 = 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

New Member
Joined
Apr 4, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
@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

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
@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

New Member
Joined
Apr 4, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,714
Members
415,922
Latest member
gemmatay88

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
Top