Create Sequence Number Countdown Rows in Table

JarekM

Board Regular
Joined
Nov 13, 2018
Messages
86
Hi, I tried to create a sequence list that would go from highest to lowest. I have found a video that does it but it only works for Excel 365 and I don't have that version. Also, the formula he used is static, but I need to be done dynamically, to the base of the rows in the data table. I need a formula or VBA code.

Here is the link:
He shows how to do it at 6:50.

Here is a picture from the video:
SEQUENCE() Function for Power List Making in Excel 365 - YouTube — Mozilla Firefox 2_14_2022 1...png
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
MrExcelPlayground7.xlsx
MNO
1highest1212
2lowest511
310
49
58
67
76
85
Sheet1
Cell Formulas
RangeFormula
O1:O8O1=N1+N2-ROW(INDIRECT(N1&":"&N2))
Dynamic array formulas.
 
Upvote 0
MrExcelPlayground7.xlsx
MNO
1highest1212
2lowest511
310
49
58
67
76
85
Sheet1
Cell Formulas
RangeFormula
O1:O8O1=N1+N2-ROW(INDIRECT(N1&":"&N2))
Dynamic array formulas.
Sorry, but this doesn't quite work. I probably just worded it incorrectly.

Here is a picture of a table that I made:
Book1 - Excel 2_14_2022 7_15_04 PM.png


What I would like is a way for the record ID to auto adjust for whenever I add a new row for suppliers or delete a row. In this case I have 5 suppliers so the ID counts down from 5 to 1, but if I were to add a new row then there would be 6 Record ID's, same if I were to remove a row then there should only be 4 Record ID's, and they should all countdown from the highest number to lowest.

I'm looking for either a formula if that works or a VBA code.
 
Upvote 0
I don't think what you just described as your intentions will work via formula.
 
Upvote 0
I would think a worksheet change event would work.
 
Upvote 0
Paste the following into the sheet module:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Target.Columns.Count = Columns.Count Then
        Dim ArrayRowNumber          As Long, ColumnRow              As Long
        Dim Column_A_DataRows       As Long, Column_A_DataStartRow  As Long
        Dim LastRowInColumn         As Long
        Dim NumberingArray          As Variant
'
        Column_A_DataStartRow = 3                                                       ' <--- Set this to the start row of data in column A
        LastRowInColumn = Range("B" & Rows.Count).End(xlUp).Row                         ' Returns a Row Number
        Column_A_DataRows = LastRowInColumn - Column_A_DataStartRow + 1                 ' Number of data rows
'
        ReDim NumberingArray(1 To Column_A_DataRows)                                    ' Set the number of rows in NumberingArray
'
        ArrayRowNumber = 0                                                              ' Initialize ArrayRowNumber
'
        For ColumnRow = UBound(NumberingArray, 1) To LBound(NumberingArray, 1) Step -1  ' Loop backwards through NumberingArray
            ArrayRowNumber = ArrayRowNumber + 1                                         '   Increment ArrayRowNumber
            NumberingArray(ArrayRowNumber) = ColumnRow                                  '   Save ColumnRow into NumberingArray
        Next                                                                            ' Loop back
'
        Range("A" & Column_A_DataStartRow).Resize(UBound(NumberingArray, 1)) = Application.Transpose(NumberingArray)    ' Display NumberingArray to column A
    End If
End Sub
 
Upvote 0
This will work unless you delete the first row:
MrExcelPlayground7.xlsx
QRST
1
2Record IDSupplierAddressPhone
35Supplier1ABC222
44Supplier5DEF333
53Supplier 10GHI444
62Supplier 3JKL555
71Supplier 6LMN666
Sheet1
Cell Formulas
RangeFormula
Q3:Q7Q3=1+COUNTA(R3:R15)-ROW(INDIRECT("1:"&COUNTA(R3:R15)))
Dynamic array formulas.


You might fiddle with the R3:R15 to make it work better. Maybe use the whole column R and account for whatever you have in the header that gets counted.
 
Upvote 0
Paste the following into the sheet module:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Target.Columns.Count = Columns.Count Then
        Dim ArrayRowNumber          As Long, ColumnRow              As Long
        Dim Column_A_DataRows       As Long, Column_A_DataStartRow  As Long
        Dim LastRowInColumn         As Long
        Dim NumberingArray          As Variant
'
        Column_A_DataStartRow = 3                                                       ' <--- Set this to the start row of data in column A
        LastRowInColumn = Range("B" & Rows.Count).End(xlUp).Row                         ' Returns a Row Number
        Column_A_DataRows = LastRowInColumn - Column_A_DataStartRow + 1                 ' Number of data rows
'
        ReDim NumberingArray(1 To Column_A_DataRows)                                    ' Set the number of rows in NumberingArray
'
        ArrayRowNumber = 0                                                              ' Initialize ArrayRowNumber
'
        For ColumnRow = UBound(NumberingArray, 1) To LBound(NumberingArray, 1) Step -1  ' Loop backwards through NumberingArray
            ArrayRowNumber = ArrayRowNumber + 1                                         '   Increment ArrayRowNumber
            NumberingArray(ArrayRowNumber) = ColumnRow                                  '   Save ColumnRow into NumberingArray
        Next                                                                            ' Loop back
'
        Range("A" & Column_A_DataStartRow).Resize(UBound(NumberingArray, 1)) = Application.Transpose(NumberingArray)    ' Display NumberingArray to column A
    End If
End Sub
Hi Johnny,
Thank you for the VBA code, it works as I wanted it to but there is one issue. The issue is that the database is another workbook, and I have all of my codes and userforms on another workbook, so to make the code work then I would have to write the code on the workbook with the data, and I don't want any code on the workbook with the data.
Is there a possibility to do this? This way I could also use it on different userforms.



This will work unless you delete the first row:
MrExcelPlayground7.xlsx
QRST
1
2Record IDSupplierAddressPhone
35Supplier1ABC222
44Supplier5DEF333
53Supplier 10GHI444
62Supplier 3JKL555
71Supplier 6LMN666
Sheet1
Cell Formulas
RangeFormula
Q3:Q7Q3=1+COUNTA(R3:R15)-ROW(INDIRECT("1:"&COUNTA(R3:R15)))
Dynamic array formulas.


You might fiddle with the R3:R15 to make it work better. Maybe use the whole column R and account for whatever you have in the header that gets counted.
Hi James, your code also works, but is there a way to make it work dynamically, or else whenever I add a new row then I would have to keep updating the formula. I also don't just want to select the whole column, because then there might be some rows that might be left empty and so on. I'm not an expert with Excel, so is there a way to also modify the formula to work dynamically, based on whether or not a row has a supplier.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top