0

In Excel, is there a way to auto populate/increment a column when a column in the same row is populated? For example, let's say I have an ID and Name Column. When I fill out the name column for a new row, I want the ID column in the same row to be auto-populate to the next number (i.e. previous ID + 1). I don't want to have to copy a formula or touch that ID field in any way.

Is that possible?

Thanks Steven

Steven
  • 1

4 Answers4

1

By magic? No such thing in Excel or any other spreadsheet.

=IF(AND(A1<>"";B2<>"");A1+1;"") <- Enter in A2 and "fill down"

e.g. Copy A2 (i.e. Place cursor in A2, CTRL+C), then...

Paste, CTRL+V, into as many rows as you wish, in column A.

Fill in a value in A1, type something in B1 - first row.
Then type something in B2, B3, B4 ...

The extreme would be ALL rows, which might slow down your spreadsheet

it is really quickly done though:

Excel:
hit End, hold SHIFT, hit Cursor down, let go of SHIFT,
CTRL+V, End, Cursor up

LibreOffice:
hold SHIFT and CTRL, hit Cursor down, let go
CTRL+V, hold CTRL, hit Cursor up

... and you have the formula in all cells of the A column, except A1.

Hannu
  • 10,568
0

If you're comfortable using VBA, the code below is a good starting point. It should be pasted into the worksheet object where you want this ID field to be. It's not perfect and you should try to understand it before you copy / paste to make sure it'll work for you. It watches a specific column for changes. Whenever something in that column changes, it checks another cell in the same row. If that other cell is blank, it fills it in with the max value in that other column plus one. This creates a numeric unique ID in a certain column.

Private Sub Worksheet_Change(ByVal Target As Range)

' Setup the columns you care about here
Const nameColumnLetter = &quot;D&quot;
Const idColumnLetter = &quot;A&quot;

' Declare some variables for easier reference later
' This is not necessary but it makes the code in the next section easier to follow
Dim idCell As Range
Dim idColumn As Range
Dim nameColumn As Range
Set idCell = Range(idColumnLetter &amp; Target.Row)
Set idColumn = Range(idColumnLetter &amp; &quot;:&quot; &amp; idColumnLetter)
Set nameColumn = Range(nameColumnLetter &amp; &quot;:&quot; &amp; nameColumnLetter)

' Check if the changed cell is in the Name column
If Not Intersect(Target, nameColumn) Is Nothing Then
    ' Check if the ID column is blank
    If Len(idCell.Value) = 0 Then
        ' Set the ID value to the max of that column plus one
        idCell.Value = Application.WorksheetFunction.Max(idColumn) + 1
    End If
End If




End Sub

How do I add VBA in MS Office?

Engineer Toast
  • 5,218
0

You can approach this at least two other ways:

  1. Make the data range a formal Excel Table. Put a formula in the ID column that looks at the cell above it and adds 1. Each time you enter anything in the next empty row Excel will add that row to the Table. When it does, it will calculate/populate that ID cell for the row. QED.

  2. If not having a Table is desired, or necessary (it can be for a minimum of two really good reasons: you don't want other column's populating formulas down because those formulas might differ or not be wanted at all and if you don't want the ID hassle, you don't want having to check and/or edit each row's formula adds to be a bigger hassle, or, a whole different thing, you need dynamic array formulas and, well, Table's just cannot have them), you can do the work yourself.

You would place the proper formula in the ID column, and wrap it in an IF. The idea would be to join all the other columns' cells in that row (&, CONCATENATE, CONCAT, TEXTJOIN... whatever works best for you) and the IF test would be does that equal "" and if it does, output "" so the cells are blank, and if it does not, then do the "row above + 1" calculation so it fills.

You might be able to make this a dynamic array formula too, so you don't have to worry about filling a few hundred or thousand extra rows/cells down so that you only have to think about it every year or two. That wouldn't be straightforward, at all, but maybe.

Jeorje
  • 1
0

There is a really simple formula to use if your data is formatted as a table. Assuming you have row 1 as column headers and your data starts on row 2, enter:

=ROW([@ID])-1
  • @ID is any column name with data.
  • -1 is used because there is one row above the start of the data. If data starts on row 3, then use -2, and so on.
ZenExcel
  • 1