This should be an easy one...

In column A I want to have a counter or series that only changes to the next value when the value in a separate column changes.

If column B is the order number, I want cell A2 to begin with a value of 1 and only move to a value of 2 when the order number changes. it would look like this

Counter Order # Product #
1 12345 65478
1 12345 52145
1 12345 65214
2 65252 98547
2 65252 65298
3 98765 96745
3 98765 63756
3 98765 12856

Thanks in advance for the help

Scott

Sabosis wrote:

This should be an easy one...

In column A I want to have a counter or series that only changes to the
next value when the value in a separate column changes.

If column B is the order number, I want cell A2 to begin with a value of
1 and only move to a value of 2 when the order number changes. it would
look like this

Counter Order # Product #
1 12345 65478
1 12345 52145
1 12345 65214
2 65252 98547
2 65252 65298
3 98765 96745
3 98765 63756
3 98765 12856

Note that if the orders aren't continguous -- say, 123 followed by 456 and
then back to 123, then this won't be accurate. (That situation would require
keeping track of all known orders and their assigned counter numbers. Not
hard, just monkey work. Probably take another 3-5 lines of code.)

Note also that this assumes that the order # is column B, and the counter is
column A.

Sub record_counter_based_on_column_value()
'assumes row 1 is header
Dim ctr, L0

'counter's start value; adjust as appropriate
Cells(2, 1).Value = 1

For L0 = 3 To Cells.SpecialCells(xlCellTypeLastCell).Row
If Cells(L0, 2).Value < Cells(L0 - 1, 2).Value Then
Cells(L0, 1).Value = Cells(L0 - 1, 1).Value + 1
Else
Cells(L0, 1).FillDown
End If
Next
End Sub

--
He straightened, assuming an odd attitude of dignity --
as though it were another mask, but this time clothing his entire body.

I found this code very useful for a project of mine. Thanks very much.
I did add a "Do..Until Loop" to stop the counter from filling any unused lines.



Sub record_counter_based_on_column_value()
'assumes row 1 is header
Dim ctr, L0

'counter's start value; adjust as appropriate
Cells(2, 1).Value = 1

For L0 = 3 To Cells.SpecialCells(xlCellTypeLastCell).Row

Do
If Cells(L0, 2).Value < Cells(L0 - 1, 2).Value Then
Cells(L0, 1).Value = Cells(L0 - 1, 1).Value + 1
Else
Cells(L0, 1).FillDown
End If
Loop Until Cells(L0, 2).Value < ""

Next
End Sub

Here's how I approach this...

I use local scope (sheet level) defined names for ranges rather
extensively (as a 'best practice') so my formulas (or code) is better
'self-documented' and so easier to understand. Local scope also allows
me to reuse the same name[s] on other sheets in the same workbook
without conflict.

Define local scope names as follows...

'Sheet Name'!DefinedName

...where the apostrophes wrap the sheet name when it contains spaces.
Otherwise, the apostrophes aren't required. Note that the exclamation
(!) character is used to delimit the string.

Make sure *A2* is selected and create the following names in the Define
Name dialog. (Assumes sheetname is "Orders"

Name1: orders!LastCell
RefersTo: =A1

Name2: orders!ThisOrderNum
RefersTo: =$B2

Name3: orders!AboveOrderNum
RefersTo: =$B1

Name4: orders!OrderCount
RefersTo: =IF(ThisOrderNum<AboveOrderNum,LastCell+1,LastCel l)

Note that while this name method works in all versions of Excel, the
later versions allow you to select the sheetname from the 'Scope:'
dropdown. (I typically copy everything from the exclamation and left to
save extra steps when entering multiple names)

How this works:
Requires A1 is *blank* because A2 needs to start at 1.
If your column *requires* a header then insert a blank row below
the headers and hide it

-OR-
manually enter 1 to start the counter in A2.

All 3 cell names are row-relative refs to the cell containing the
formula using those names;

Names 2&3 are column-absolute so your counter can be used in any
column you wish;

Enter the following formula in A2 and copy down...
=OrderCount
..or select all cells to receive the formula, enter it once, then
use Ctrl+Enter to populate the selection.

This method will auto-adjust if rows are inserted/deleted.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus