0

[Excel 2013] I have a large list of items and I want to add a preference number to every row manually based on my preference. Example below:

Value   Preference
Row1    6
Row2    _
Row3    2
Row4    _
Row5    3
Row6    1
Row7    5
Row8    4

Currently, I choose the row and add the preference number manually. This gets tiring especially as I go to 3-digits. I want excel to help with auto-incrementing in any row I choose. For example, I choose Row4 next, and 7 should be inserted as Preference. And then, if I choose Row2, 8 should be inserted as Preference.

2
  • does you preference follow some logic?
    – yass
    Apr 9, 2017 at 9:46
  • No. It's assigned by the user based on Value.. My actual use case was assigning preferences to a huge list of college/courses for counseling.
    – aalosious
    Apr 21, 2017 at 9:05

2 Answers 2

1

The macro would be:

Sub enterNextNumber()
    If ActiveCell.Value = "" Then ActiveCell.Value = WorksheetFunction.Max(Columns(ActiveCell.Column)) + 1
End Sub

You could link it to a shortcut like Ctrl+I, then you'd just select your next cell, press the shortcut and it'll enter the next number in your sequence by looking at the largest existing number in the active column and adding 1.

0

Thanks, David. The macro does exactly what I was looking for..

I had come up with a non-macro solution after posting the question. It seems to work faster than the macro based solution in tables with some 10k rows.

Column B is my Preference column.

  1. Click Formulas ribbon > Calculation Options > Choose "Manual".
  2. Paste "=MAX(E:E)+1" in whichever cell of B I want the next preference to be in.
  3. Once I'm done with assigning preferences, turn back Calculation Options to "Automatic".
  4. Copy Column B and Paste (text only) in an empty Column, and then copy back to B, to be safe (prevent any accidental update).

This is less elegant than David's Macro solution.. but for someone unfamiliar with macros, this might help.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .