Excel data numbering

Question

I have a series of data in excel as under

ARG2755-NNL4W
BBF1732-NNM4WF
BES0542-NNM4W
BES0542-NNM4W
BES0542-NNM4W
BES0542-NNM4W
BES0542-NNM4W
BES0542-NNM4W
BRA0743-NNM4VF
BRA1650-NNM4TK
BRA1919-NNO1PK
BRA1919-NNO1WK
BRA1920-NNL4VK
BRA1920-NNL4VK
BRB0939-NNL4WF
BRB1625-NNL4WK
BRB1625-NNL4WK

I want to number it as under:

1   ARG2755-NNL4W
2   BBF1732-NNM4WF
3   BES0542-NNM4W
    BES0542-NNM4W
    BES0542-NNM4W
    BES0542-NNM4W
    BES0542-NNM4W
    BES0542-NNM4W
4   BRA0743-NNM4VF
5   BRA1650-NNM4TK
6   BRA1919-NNO1PK
7   BRA1919-NNO1WK
8   BRA1920-NNL4VK
    BRA1920-NNL4VK
9   BRB0939-NNL4WF
10  BRB1625-NNL4WK
    BRB1625-NNL4WK

Please help

score 4 · Answer 1 · answered Jan 15 '16 at 15:03

4

With data in column B, in A1 enter:

In A2 enter:

=IF(B2=B1,"",1+MAX($A$1:A1))

and copy down:

answered Jan 15 '16 at 15:03

Gary's Student

19,636

score 3 · Answer 2 · answered Jan 15 '16 at 12:07

Assuming columns are A and B, put a 1 in column A1, then put this formula in A2 and drag it down

=IF(NOT(B2=B1),INDEX($A$1:A1,MATCH(9.99999999999999E+307,$A$1:A1))+1,"")

This works by:

=IF(NOT(B2=B1)... - Compares cell B2 to B1. If they don't match...

INDEX($A$1:A1,MATCH(9.99999999999999E+307,$A$1:A1))+1 - This finds the last number from A1 (Fixed), to A1 (Variable), and adds 1 to it. The $'s are needed to make sure it's always comparing from A1, down to the cell just before it. Otherwise:

"") - Just return a blank string.

score 2 · Answer 3 · edited Mar 20 '17 at 10:17

This VBA does it. Do this on a copy of your workbook as there is no undo option.

Also note, you can set some values at the start of the code

Option Explicit
Sub SailMeHearties()

Dim row As Integer
row = 1

Dim col As String
col = "B"

Dim colOfNumber As String
colOfNumber = "A"

' LEAVE THE CODE BELOW ALONE OR WALK THE PLANK

Range(colOfNumber + ":" + colOfNumber).Clear
Range(colOfNumber & row).Value = 1

Dim startNumber As Integer
startNumber = 2

row = row + 1

Do While Range(col & row).Value <> ""

    If (Range(col & row).Value <> Range(col & row - 1).Value) Then
        Range(colOfNumber & row).Value = startNumber
        startNumber = startNumber + 1
    End If
    row = row + 1
Loop

End Sub

Also see How do I add VBA in MS Office?

Excel data numbering

3 Answers3