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

Do you need the blanks, or would it be acceptable to show tied values? Also what have you tried? — Rowland Shaw, Jan 15 '16 at 11:34
@NIMISHAN, can you expand on your comment, because as it is, it's not very helpful (when you consider there are already 2 answers, none of which use either approach you've suggested) :( If you know how to to do it please share because you may have a better solution than the answers posted :) — Dave, Jan 15 '16 at 12:22

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,266
6
25
39

1

Well that's much neater. +1 – Jonno Jan 15 '16 at 16:52

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

3

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.

answered Jan 15 '16 at 12:07

Jonno

21,049
4
61
70

`INDEX($A$1:A1,MATCH(9.99999999999999E+307,$A$1:A1))+1` - See, when comparing VBa to this compact nightmare, VBa seems so much clearer. Regardless, I feel your answer is better than mine as it is with worksheet formula so +1 :) – Dave Jan 15 '16 at 12:17
@Dave Most formulas feel horribly hacky. But they are easier for the end user usually. I like yours for ease of reading, definitely. Even if there was just a way to indent formulas or something, rather than having nests of hundreds of brackets on a single line... – Jonno Jan 15 '16 at 12:20
@Dave And I'm sure that 9.999999999etc part could be like, 1000, but this way prevents it breaking if the number is too high. – Jonno Jan 15 '16 at 12:21

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