Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 47
Default record counter based on column value

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default record counter based on column value

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default record counter based on column value

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default record counter based on column value

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple record counter scubadiver Excel Programming 1 January 19th 09 01:07 PM
Copy Record to Appropriate Worksheet Based on Contents of Column A - VBA. Si[_2_] Excel Programming 7 February 2nd 07 08:33 AM
Should I use Do-While loop for my record counter? excelnut1954 Excel Programming 0 March 24th 06 10:25 PM
Can somebody explain to me how Bob Phillips' unique record counter works? Ken Johnson Excel Programming 4 September 22nd 05 10:59 PM
Program Column B to record numerical range based on number in colm Nikole Excel Discussion (Misc queries) 2 August 17th 05 08:37 PM


All times are GMT +1. The time now is 04:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"