Assigned Unique Number

[itsgrady]

I can give the row in the table a unique number by using a calculation but when the table is sorted the number is not sorted with the row. A new number is assigned.

Is there a way to have excel to assign the numbers on each row of the table but when sorted the number stays with the row?

When I add a the row to table the number is there. Like a checkbook number.

Thanks for the help.

 

[Joe4]

What exactly is the formula you are using?
Not that formulas will be recalculated when the data is sorted.
If you want to give it a number that does not change, and you do not want to do that manually, you would probably need to use VBA.

 

[itsgrady]

What exactly is the formula you are using?
Not that formulas will be recalculated when the data is sorted.
If you want to give it a number that does not change, and you do not want to do that manually, you would probably need to use VBA.

What’s is a good VBA code? The number is just in sequence. But I do have a letter in front of the number.

 

[Joe4]

Please show me some examples, and answer the following questions:

Is it always the same letter at the beginning?
How many numbers is the numeric portion?
What should "trigger" this value to be populated?
What column is this value being placed in?
Are records every delete so that there may be "gaps" in your sequence?

 

[itsgrady]

Please show me some examples, and answer the following questions:

Is it always the same letter at the beginning?
How many numbers is the numeric portion?
What should "trigger" this value to be populated?
What column is this value being placed in?
Are records every delete so that there may be "gaps" in your sequence?

Thanks for the help

Yes, the same letter Each time (ex:P-01).
2 numbers for the numeric portion.
Trigger-Adding a new line a table.
Column B is where the number is being placed.
Records should be voided not deleted.

Would like to stop them deleting a row.

 

[Joe4]

When adding a new line in a table, are they just going to the bottom of the list and typing in the values needed, or are they actually inserting a new row into an actual Excel table?
What columns are they entering data into?
For each row with data in it, which columns are REQUIRED to have data entered into them?

Would like to stop them deleting a row.

That is a whole different question. How to accomplish that depends on whether you are working with an official "table" or not.
You should post that question to its own thread.

 

[itsgrady]

When adding a new line in a table, are they just going to the bottom of the list and typing in the values needed, or are they actually inserting a new row into an actual Excel table?
What columns are they entering data into?
For each row with data in it, which columns are REQUIRED to have data entered into them?


That is a whole different question. How to accomplish that depends on whether you are working with an official "table" or not.
You should post that question to its own thread.

They are actually inserting a new row. Also, the can insert a lot row then go back and enter the information.

 

[Joe4]

They are actually inserting a new row. Also, the can insert a lot row then go back and enter the information.

OK, then the other two questions I asked in my last reply are very important, so please answer them.

 

[Joe4]

Assuming that your header/title row is row 1, and the first row of your data starts on row 2, and assuming that column A always need to be populated for every row of data, the following VBA with automatically populate column B with the next number in line, no matter where it is inserted.

NOTE: This code MUST be placed in the proper sheet module in VBA. One easy way to ensure it gets put in the proper place is to right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim ct As Long

'   Check to see if column A populated and column B is blank
    Set rng = Intersect(Target, Columns("A:A"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells just updated in column A
    For Each cell In rng
'       Check to see if column B is empty and column A is not
        If (cell <> "") And (cell.Offset(0, 1) = "") Then
'           Count number of current entries in column B
            ct = Application.WorksheetFunction.CountA(Columns("B:B"))
'           Update column B with new ID
            Application.EnableEvents = False
            cell.Offset(0, 1) = "P-" & Format(ct, "00")
            Application.EnableEvents = True
        End If
    Next cell
    
End Sub

 

[itsgrady]

Assuming that your header/title row is row 1, and the first row of your data starts on row 2, and assuming that column A always need to be populated for every row of data, the following VBA with automatically populate column B with the next number in line, no matter where it is inserted.

NOTE: This code MUST be placed in the proper sheet module in VBA. One easy way to ensure it gets put in the proper place is to right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim ct As Long

'   Check to see if column A populated and column B is blank
    Set rng = Intersect(Target, Columns("A:A"))
    If rng Is Nothing Then Exit Sub
   
'   Loop through cells just updated in column A
    For Each cell In rng
'       Check to see if column B is empty and column A is not
        If (cell <> "") And (cell.Offset(0, 1) = "") Then
'           Count number of current entries in column B
            ct = Application.WorksheetFunction.CountA(Columns("B:B"))
'           Update column B with new ID
            Application.EnableEvents = False
            cell.Offset(0, 1) = "P-" & Format(ct, "00")
            Application.EnableEvents = True
        End If
    Next cell
   
End Sub

I’ll work on it on tomorrow. I appreciate the help a lot.