Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Multiple record counter | Excel Programming | |||
Copy Record to Appropriate Worksheet Based on Contents of Column A - VBA. | Excel Programming | |||
Should I use Do-While loop for my record counter? | Excel Programming | |||
Can somebody explain to me how Bob Phillips' unique record counter works? | Excel Programming | |||
Program Column B to record numerical range based on number in colm | Excel Discussion (Misc queries) |