Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list like this:

    A               B
Student ID     Student Name

20496836       Barnes, Troy
28682693       Bennett, Shirley
               Edison, Annie
28395839       Hawthorne, Pierce
               Perry, Britta
               Nadir, Abed
23234242       Winger, Jeff

I need to assign student IDs in sequence to the ones that do not have them, starting with 90000000. In other words:

    A               B
Student ID     Student Name

20496836       Barnes, Troy
28682693       Bennett, Shirley
90000000       Edison, Annie
28395839       Hawthorne, Pierce
90000001       Perry, Britta
90000002       Nadir, Abed
23234242       Winger, Jeff

I have about 1000 of these to fill in, so I need to find a way to do it in one swoop.

share|improve this question
add comment

3 Answers

up vote 8 down vote accepted

To do it in one "swoop", you can select the column where you need to add the numbers (for this example, select A2 to A8).

Then hit Ctrl+G and select Special... > "Blanks" > OK.

Press = (without touching anything else) and paste this:

90000000+COUNTIF($A$2:A3,">="&90000000)

Now, do NOT press Enter but press Ctrl+Enter. After this, you should be good to go, but to avoid any accident, copy and paste values on column A so that the results cannot be changed again.

share|improve this answer
add comment

I don't know about doing this using VBA, but you could do this by:

  1. set column C to be 0 or 1 depending on if the the value in column A is blank (=if(A2="", 1, 0))
  2. set column D to be the ID using a formula like (=if(A2="", 90000000+sum($A$2:A2)-1, A2))

Untested . . .

share|improve this answer
add comment

(Answer edited from 8/21:)

To do in VBA, insert this code into a macro. The key is the .filldown method.

This assumes the last row in your sheet can be found in column B, and there are no gaps in B. It also assumes the 2nd row is blank to seperate the header from the rest of the data as you've shown above.

'last_row is based on the last name in column B, ignoring the first 2 rows,
'and will be the point at which this macro stops as it works from the top
'down.
'next_row is a look-ahead to see what row needs to be acted on next, as
'the macro progresses, so fills in student IDs in the appropriate spaces
Dim last_row As Long, next_row As Long
last_row = ActiveSheet.Range("B3").End(xlDown).Row

'student_id is the student ID, starting at 90000000
Dim student_id As Long
student_id = 90000000

Cells(3, 1).Select 'starting point in column A, ignoring the first 2 rows

'the meat & potatoes
Do While Selection.Row < last_row
  'if there is a space after the current row, fill in student_id
  If Selection.Row <> Selection.End(xlDown).Row - 1 Then
    'find the next row to advance to before changing anything
    next_row = Selection.End(xlDown).Row
    'put in the student ID >= 90000000 in the space after the
    'current row
    Selection.Offset(1).Value = student_id
    'fill in that student ID in all blank space(s)
    Range(Selection.Offset(1), Selection.End(xlDown).Offset(-1)).FillDown
    'add 1 to the student ID
    student_id = student_id + 1
    Cells(next_row, 1).Select
  Else
  'if no space after current row, progress 1 row
    Selection.Offset(1).Select
  End If
Loop
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.