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
  • 1
  • 1

3 Answers3

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
  • 8,740
  • 3
  • 21
  • 39
  • 1
    I don't think it's a good idea to fill tens / hundreds of thousands of cells with formulas without a real use. The same could be achieved with a table without any extra cell. – Máté Juhász Aug 02 '21 at 19:45
  • Please take this not as literal, but as a way to communicate an idea. – Hannu Aug 02 '21 at 20:04
  • @MátéJuhász - Facts: In LibreOffice 6.4.x the filling takes less than two seconds, that is into 1,048,576 cells. There is no notable lagging as you fill in text in column B. This on an i7-4770-based computer. might be that spreadsheets are more effective doing calculations than you expect. – Hannu Aug 03 '21 at 16:26
  • you're right, having just this column won't cause any observable delay. The problem will occur when a bit more complex calculation will use it. And the user won't understand why the spreadsheet is becoming slower. – Máté Juhász Aug 03 '21 at 16:58
  • ... and we're back to the basics. The above is there to show a feature, not to imply that you MUST use it exactly like that. If not anything else then you actually learnt or realized something. And one more thing... these lines also provide insight. – Hannu Aug 07 '21 at 05:00
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 = "D"
    Const idColumnLetter = "A"
    
    ' 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 & Target.Row)
    Set idColumn = Range(idColumnLetter & ":" & idColumnLetter)
    Set nameColumn = Range(nameColumnLetter & ":" & 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
  • 4,955
  • 1
  • 20
  • 33
  • My early experience with borrowed code containing `Worksheet_Change()` and others locking up Excel; made me avoid these as the plague. It is easy to build forever looping code with these. – Hannu Aug 03 '21 at 16:09
  • @Hannu That is entirely possible if the `Worksheet_Change()` event contains code that changes the worksheet. The way this code is written, though, it makes changes to column A if and only if there was a change in column D so it will not trigger itself. – Engineer Toast Aug 03 '21 at 17:20
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