|
Excel function, incrementing value
I have a need to generate an incrementing number in a list. The
number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001 |
Excel function, incrementing value
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row Range("B2").Value = 1 For i = 3 To LastRow If .Cells(i, TEST_COLUMN).Value = _ .Cells(i - 1, TEST_COLUMN).Value Then .Cells(i, "B").Value = .Cells(i - 1, "B").Value + 1 Else .Cells(i, "B").Value = 1 End If Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have a need to generate an incrementing number in a list. The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001 |
Excel function, incrementing value
On Jan 2, 12:27*pm, "Bob Phillips" wrote:
Public Sub ProcessData() Const TEST_COLUMN As String = "A" * *'<=== change to suit Dim i As Long Dim LastRow As Long * * With ActiveSheet * * * * LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row * * * * Range("B2").Value = 1 * * * * For i = 3 To LastRow * * * * * * If .Cells(i, TEST_COLUMN).Value = _ * * * * * * * * .Cells(i - 1, TEST_COLUMN).Value Then * * * * * * * * .Cells(i, "B").Value = .Cells(i - 1, "B").Value + 1 * * * * * * Else * * * * * * * * .Cells(i, "B").Value = 1 * * * * * * End If * * * * Next i * * End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have a need to generate an incrementing number in a list. *The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. *This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. *Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001- Hide quoted text - - Show quoted text - Thanks very much. Do I need to name Column A in the excel sheet TEST_COLUMN. How do I apply the vba code to column 2. Sorry, I'm a newbie. -- Dave |
Excel function, incrementing value
On Jan 2, 12:27*pm, "Bob Phillips" wrote:
Public Sub ProcessData() Const TEST_COLUMN As String = "A" * *'<=== change to suit Dim i As Long Dim LastRow As Long * * With ActiveSheet * * * * LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row * * * * Range("B2").Value = 1 * * * * For i = 3 To LastRow * * * * * * If .Cells(i, TEST_COLUMN).Value = _ * * * * * * * * .Cells(i - 1, TEST_COLUMN).Value Then * * * * * * * * .Cells(i, "B").Value = .Cells(i - 1, "B").Value + 1 * * * * * * Else * * * * * * * * .Cells(i, "B").Value = 1 * * * * * * End If * * * * Next i * * End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have a need to generate an incrementing number in a list. *The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. *This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. *Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001- Hide quoted text - - Show quoted text - OK. Then do I need to name column A TEST_COLUMN? I'm not sure how i apply this code to column B. I've copied it into VB Editor successfully. Thanks, Dave |
Excel function, incrementing value
Here's a formula but this may not be very efficient if you have 1000's of
rows of data: =COUNTIF(A$1:A1,A1) Custom format as 0000 -- Biff Microsoft Excel MVP wrote in message ... I have a need to generate an incrementing number in a list. The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001 |
Excel function, incrementing value
In B2: =TEXT(COUNTIF(A$2:A2,A2),"0000")
copy down as far as needed " wrote: I have a need to generate an incrementing number in a list. The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001 |
Excel function, incrementing value
wrote in message
... On Jan 2, 12:27 pm, "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row Range("B2").Value = 1 For i = 3 To LastRow If .Cells(i, TEST_COLUMN).Value = _ .Cells(i - 1, TEST_COLUMN).Value Then .Cells(i, "B").Value = .Cells(i - 1, "B").Value + 1 Else .Cells(i, "B").Value = 1 End If Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have a need to generate an incrementing number in a list. The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001- Hide quoted text - - Show quoted text - ********** OK. Then do I need to name column A TEST_COLUMN? I'm not sure how i apply this code to column B. I've copied it into VB Editor successfully. Thanks, Dave ********** The macro assumes your data is in column A. Const TEST_COLUMN As String = "A" '<=== change to suit Change the "A" to reflect the actual column your data is in. The macro also assumes that since the data is in column A, then you want the count to appear in column B starting in cell B2. Change "B2" to reflect where you want the count to start. So, if you want the count in some other column change every instance of "B" to reflect the column where you want the count to appear. -- Biff Microsoft Excel MVP |
Excel function, incrementing value
"T. Valko" wrote in message
... wrote in message ... On Jan 2, 12:27 pm, "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row Range("B2").Value = 1 For i = 3 To LastRow If .Cells(i, TEST_COLUMN).Value = _ .Cells(i - 1, TEST_COLUMN).Value Then .Cells(i, "B").Value = .Cells(i - 1, "B").Value + 1 Else .Cells(i, "B").Value = 1 End If Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have a need to generate an incrementing number in a list. The number will be 0001, 0002, 0003, etc., until the change occurs on a value in another column. In the example below, when WYE465 changes to WYE565, the incrementing number restarts at 0001. This needs to occur again when the value in the first column changes to a new value ( e.g., WYE566 ). Any assistance in creating a formula in excel that will perform this would be greatly appreciated. Thanks. Table: value incrementing count WYE465 0001 WYE565 0001 WYE565 0002 WYE565 0003 WYE565 0004 WYE565 0005 WYE565 0006 WYE565 0007 WYE565 0008 WYE565 0009 WYE565 0010 WYE565 0011 WYE565 0012 WYE566 0001- Hide quoted text - - Show quoted text - ********** OK. Then do I need to name column A TEST_COLUMN? I'm not sure how i apply this code to column B. I've copied it into VB Editor successfully. Thanks, Dave ********** The macro assumes your data is in column A. Const TEST_COLUMN As String = "A" '<=== change to suit Change the "A" to reflect the actual column your data is in. The macro also assumes that since the data is in column A, then you want the count to appear in column B starting in cell B2. Change "B2" to reflect where you want the count to start. So, if you want the count in some other column change every instance of "B" to reflect the column where you want the count to appear. -- Biff Microsoft Excel MVP P.S. You might also have to change this line: For i = 3 To LastRow It would be easier if you just told us where the data is and where you want the count. -- Biff Microsoft Excel MVP |
| All times are GMT +1. The time now is 09:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com