Insert a Sequence Number

[am465]

I would like to insert a sequence number into a query that currently returns 7367 rows.

Here's what it looks like now:

ID_NUM	DTE_FIN_TRANS_RCV
1322	9/27/2012
1322	7/10/2012
1011	7/2/1999
1011	9/22/1999
1011	10/21/1999

<tbody>
</tbody>

Here's the desired outcome with sequences:

ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
1322	9/27/2012	1
1322	7/10/2012	2
1011	7/2/1999	1
1011	9/22/1999	2
1011	10/21/1999	3

<tbody>
</tbody>

All I need is a way to number rows with a value higher than the previous for that particular ID_NUM. Any assistance with achieving this will be most appreciated. Thank you.

 

[am465]

...

 

[hiker95]

am465,

How about something like this:

Excel 2007
	A	B	C
1	ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
2	1322	9/27/2012	1
3	1322	7/10/2012	2
4	1011	7/2/1999	1
5	1011	9/22/1999	2
6	1011	10/21/1999	3
7
Sheet1

The formula in cell C2, copied down:
=COUNTIF($A$2:A2,A2)

 

[hiker95]

am465,

I assume that your raw data is already sorted/grouped by the data in column A, per your example.

Or, with a macro:

Sample raw data:

Excel 2007
	A	B	C
1	ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
2	1322	9/27/2012
3	1322	7/10/2012
4	1011	7/2/1999
5	1011	9/22/1999
6	1011	10/21/1999
7
Sheet1

After the macro:

Excel 2007
	A	B	C
1	ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
2	1322	9/27/2012	1
3	1322	7/10/2012	2
4	1011	7/2/1999	1
5	1011	9/22/1999	2
6	1011	10/21/1999	3
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub InsertSequenceNumber()
' hiker9, 11/11/2013
' http://www.mrexcel.com/forum/microsoft-access/738615-insert-sequence-number.html
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Range("C2:C" & lr)
  .Formula = "=COUNTIF($A$2:A2,A2)"
  .Value = .Value
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertSequenceNumber macro.

 

[xenou]

I tried using a correlated query on this without success (the dreaded "query must be updateable" message). That leaves iterating a recordset which is a little awkward though not terribly difficult.

Is it absolutely necessary. The dates already provide an implicit ordering. What does the sequence numbering provide that you don't already have?

PS:
@Hiker, welcome to the *Access* forum

 

[hiker95]

xenou,

@Hiker, welcome to the *Access* forum

I am not sure how I missed that.