Counting Values in a different column

[gaby58]

Hello,

I have a field like this and want to put values in the next column this way, how do i do this?

abc 001
abc 002
xyz 001
xyz 002
xyz 003
xyz 004
xyz 005
xyz 006
xyz 007
xyz 008
xyz 009
xyz 010
xyz 011 and so on ..

any tips are appreciated

thanks

[Leith Ross]

Hello Gaby58,

I have 2 questions. First, is the first column (Abc, Xyz) already sorted? Second, are you looking for a VBA macro or a Worksheet formula?

Thanks,
Leith Ross

[gaby58]

First row is already sorted, i prefer a work sheet formula if it is possible..

[Leith Ross]

Hello Gaby58,

I couldn't do this with Worksheet Formula, but did create a VBA macro to do this. The macro is designed so all you need to do is select the first cell of the range and press Ctrl+Shift+C to run the macro. It will determine the length of the column and then automatically fill in the count in the cell immediately to the right, as in your example. I will walk you through how to install the macro in your workbook.
_________________________________________________________________

Public Sub CountRepeats()

'Short Cut Keys = Ctrl + Shift + C (In Workbook_Open Event)
'Using the Active Cell on the Active Worksheet...
'Search a sorted list and count repeats by placing a serial
'count in the adjacent column to the Right of the Active Cell.
'
'Example:
' Active Cell = "A1"
' CountRepeats
'
' | A | B |
'------------
'1| abc | 1 |
'2| abc | 2 |
'3| xyz | 1 |
'4| xyz | 2 |
'5| xyz | 3 |

Dim I As Long
Dim C As Long
Dim N As Long
Dim R As Long
Dim Wks As Worksheet

Set Wks = ActiveSheet
With Application.ActiveCell
C = .Column
R = .Row
End With

N = 1

For I = R To Wks.Cells(Rows.Count, C).End(xlUp).Row
Wks.Cells(I, C + 1).Value = N
If Wks.Cells(I, C).Value = Wks.Cells(I + 1, C).Value Then
N = N + 1
Else
N = 1
End If
Next I

End Sub
_________________________________________________________________

Installing the Macro:

1) Select all the code above between the black lines and copy it using Ctrl + C.
2) Open your Workbook if it isn't already
3) Press Alt + F11 to open the VBA editor
4) Next Press Alt + I to Select Insert
5) Press M, this inserts a standard module that will hold the macro code
6) Paste the code using Ctrl + V
7) Save the code by pressing Ctrl + S

Assigning the macro's shortcut key.

1) If the VBA Editor isn't visible press Alt + F11 to open it.
2) In the Upper Left Corner you should see a window titled Project - VBA Project
3) If this window is not visible, press Ctrl + R to bring it up
4) Scroll down using the scroll bar on the right, and look for ThisWorkbook
5) Double Left Click this entry.
6) Select all the code below between the black lines and copy it using Ctrl + C
7) On the Right Side Left Click below the top line Private Sub Workbook_Open()
8) Paste the code using Ctrl + V
9) Save the code by pressing Ctrl + S
_________________________________________________________________

Application.OnKey "^+C", "CountRepeats"
_________________________________________________________________

I hope the instructions are clear and you aren't overwhelmed. If you run in to any problems, you can contact me here at the Forum or by e-mail to [email protected].

Best Wishes,
Leith Ross

[GaryDK]

This formula will do the counting -

=COUNTIF(A$1:A1,A1)

Gary