0

I got a requirement for auto filling the row with unique number starting from some value(say 1000).

  • The sheet has 2 columns.

  • When ever I fill a value in 2nd column in each row the first column should auto filled.

  • I have filled 3 rows(1000,1001,1002).

  • Now if i delete the middle row that has the auto generated value as 1001, the row that was initially with the value 1002 gets automatically updated to 1001.

  • But according to my requirement the value should remain unique(it should remain as 1002).

Formula that i used is:

=IF(B2<>"",COUNTA($B$2:B2)+999,"")

My excel is like this before deleting the value:

Test_Case_Id    Name
1000             a
1001             b
1002             c

After deleting the middle row it becomes:

Test_Case_Id    Name
1000             a
1001             c

Expected Result:

Test_Case_Id    Name
1000             a
1002             c

Please help me in this.

Thanks, Vevek

Community
  • 1
  • 1
Vevek
  • 43
  • 1
  • 2
  • 6

3 Answers3

2

You tagged with VBA, but you are not using VBA. Formulas that count rows or use the current row number will always update when the sheet re-calculcates.

In order to achieve what you describe, you really need VBA. A simple worksheet change event should do it. Think along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxNumber
If Not Intersect(Target, Range("B:B")) Is Nothing Then
' don't run when more than one row is changed
    If Target.Rows.Count > 1 Then Exit Sub
' if column A in the current row has a value, don't run
    If Cells(Target.Row, 1) > 0 Then Exit Sub
' get the highest number in column A, then add 1 and write to the
' current row, column A
    maxNumber = Application.WorksheetFunction.Max(Range("A:A"))
    Target.Offset(0, -1) = maxNumber + 1
End If
End Sub

Copy the code, right-click the sheet tab, select "view code" and paste into the code window.

Here's a screenshot of before and after deleting row with ID 1002:

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • With this VBA only auto increment works fine. It doesnt meet the requirement of having the same value after deleting the pervious row count value. – Vevek Jan 25 '16 at 08:49
  • What do you mean? If a row is deleted, the remaining numbers stay in place and don't adjust. That is what you show in your "expected result" in the question. – teylyn Jan 25 '16 at 19:32
  • I added a screenshot. Please tell me what does not work according to the requirements. – teylyn Jan 25 '16 at 19:36
  • Hi. It was my fault. I have used the macro with the formula, so it behaved differently. The macro is working perfect.Thanks. – Vevek Jan 27 '16 at 06:26
  • Thanks for the feedback and for accepting my answer. – teylyn Jan 27 '16 at 07:06
0
Sub abc()
Dim a As Integer
Dim b As Integer
Dim i As Integer
Dim z As Integer

i = 1
Set sh = ThisWorkbook.Sheets("Sheet1") 'instead of sheet1 you can add name of your sheet
Dim k As Long
k = 2
Set rn = sh.UsedRange
k = rn.Rows.Count

Cells(1, 1).Value = 1001


Do Until i = k

b = (Cells(i, 1).Value)
a = b + 1
i = i + 1

Cells(i, 1).Value = a

Loop
End Sub

Not sure if you need a macro. You can add this macro to your file. Even after you delete the cell, once you run again the macro, the current cell value will be the value of the above cell plus one, so everthing will be according to series. Thanks

teylyn
  • 34,374
  • 4
  • 53
  • 73
Sayyed Abbas Rizvi
  • 102
  • 1
  • 9
  • Does your keyboard have keys for `,` (comma) and `.` (dot or full stop)? If so, why don't you use them? Punctuation was invented to make written communication understandable. It tells your readers where a sentence ends or where to insert a pause. Omitting punctuation renders a wall of text that is hard to comprehend. Your explanation is a mess. – teylyn Jan 25 '16 at 07:59
  • sorry @teylyn Thanks for your feedback – Sayyed Abbas Rizvi Jan 25 '16 at 08:18
  • For auto increment, the one line formula that I have mentioned is working good. My main requirement is having the unique value for each row. When i delete a row, the next row's id should not get modified.Thanks. – Vevek Jan 25 '16 at 08:54
  • if you want you can add a button and associate the macro with it, So all this job is done just with a click.All cells in the column remains unique and according to the series as well. – Sayyed Abbas Rizvi Jan 25 '16 at 09:19
  • @Vevek You need to Run the Vba again after deleting ....==> Enter Data in Second Column . Try these Steps (1) Run the macro (2) Delete rows (3) Run the macro again – Sayyed Abbas Rizvi Jan 25 '16 at 09:20
  • Thanks. This macro gives auto incremented values only. If i delete a row, then the id of next row gets modified. – Vevek Jan 25 '16 at 09:43
0

WHAT IF YOU USE THE FOLLOWING TO CREATE A UNIQUE ID #

=TEXT(NOW(),"YYYYMMDD-") & TEXT(1,"0000")

20210728-0001 TASK 0001 20210728-0002 TASK 0002 20210728-0003 TASK 0003

HOWEVER NOW THE 0002 ITEM NEEDS TO BE DELETED

HOW CAN THE UNIQUE ID # REMAIN WITH THE ASSOCIATED TASK

20210728-0001 TASK 0001 20210728-0003 TASK 0003

user16549451
  • 1
  • Don't yell. Don't ask questions in answers. Do format properly https://stackoverflow.com/editing-help Do take the [tour]. Do read [answer]. – Yunnosch Jul 28 '21 at 21:04