1

I have a list of names and I wish to create a column next to this indicating what number of entry in the list every given name is. An example of what I am trying to achieve, the column entitled "ENTRY", is below,:

INDEX NAME    ENTRY
1     MIKE    1
2     JANE    1
3     JANE    2
4     JANE    3
5     LANA    1
6     MIKE    2
7     JEREMY  1
8     CLIVE   1
9     JANE    4

How can I achieve this?

1
  • I think your question is clear, so +1, however, you don't explain why you want this...so I'm making a few assumptions and as such I don't feel you can do this without VBa. Is that OK?
    – Dave
    Jul 29, 2015 at 11:19

2 Answers 2

1

This VBa does what you want.

Remember to take a back up fo the file first, there is no undo function

Option Explicit
Sub EeekPirates()

Dim startRow As Integer
startRow = 2                          'Ah hoy cap'ain, enter the startRow

Dim entryColumn As String
entryColumn = "C"                     'Enter the entry column so we can place the gold...

Dim nameColumn As String
nameColumn = "B"                      'Enter the name column so we know the sea dogs name

'aye aye cap'ain, we'll start now. Touch any of the below and we'll feed ye to the sharks

Dim realStartRow As Integer
realStartRow = startRow


Dim innerRow As Integer
innerRow = startRow
Do While (Range(nameColumn & startRow).Value <> "")
Dim count As Integer
count = 0
Dim name As String
name = Range(nameColumn & startRow).Value
    Do While (innerRow - 1 < startRow)
        If (Range(nameColumn & innerRow).Value = name) Then
            count = count + 1
        End If

        innerRow = innerRow + 1
    Loop
    Range(entryColumn & startRow).Value = count
    innerRow = realStartRow
    startRow = startRow + 1
Loop


End Sub

How do I add VBA in MS Office?

And a screen shot of what it looks like after the VBa is executed

enter image description here

1
  • This works perfectly. Excellent. Many thanks for your help. Jul 29, 2015 at 11:41
2

Without VBA

In C2 enter 1, in C3 enter:

=COUNTIF($B$2:B3,B3)

and copy down:

enter image description here

3
  • +1 better answer than mine
    – Dave
    Jul 29, 2015 at 11:53
  • @Dave Your answer is also good! Jul 29, 2015 at 11:53
  • Excellent. This also works. I'll leave answer as is but will mark this up when I have sufficient reputation. Many thanks for your help. Jul 29, 2015 at 11:55

You must log in to answer this question.

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