Adding 1 to an existing number if criteria is matched?

Solved/Closed
Report
Posts
2
Registration date
Tuesday June 4, 2013
Status
Member
Last seen
June 5, 2013
-
 Blocked Profile -
In column A I have a set of initials and in column C I want a number that corresponds to the number of times the initials have appeared...

Column A Column C
xx 1
xy 1
xy 2
xx 2
xy 3
xx 3

This is what I want it to look like.. Also if I could have any of the numbers in a different color for person xx than person xy.. and is there a way to add through multiple sheets.. its a yearly spreadsheet so its broken down into months.. and I want the numbers to roll over to the next month if possible..

Thanks in advance!!

4 replies

Posts
2
Registration date
Tuesday June 4, 2013
Status
Member
Last seen
June 5, 2013

Could use some help.. pulling my hair out.. =(

I have looked at this a number of different ways.

First one, you would have to write a macro that starts at the a1 cell and continues to index by 1, and see is a value there. If it is then store it, and check the next cell. and so on.....then seprate all arrays that are stored and present them into a finished cell for reporting.

Or, you can highlight the cells you wish to have indexed, and filter them. That is a built in funtion of Excel.

Otherwise, you really need to be using a DB for records, then you can query for all records with "XX" in the name, and sum them.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi RShelton,

Please leave your hair alone, it will leave you eventually. ;)

The following code will work for one sheet:
Sub CountInitials()
Dim x, y, z, lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
z = 1

For Each cell In Range("A1:A" & lRow)
    x = z
    y = 0
    If cell.Offset(0, 1).Value = vbNullString Then
        cell.Offset(0, 1).Value = y + 1
        y = y + 1
        Do
            x = x + 1
            If Range("A" & x).Value = cell.Value Then
                Range("B" & x).Value = y + 1
                y = y + 1
            End If
        Loop Until x = lRow
    End If
    z = z + 1
Next cell

End Sub

To count for all sheets I would paste them all below each other in one sheet if possible.

And what are your thoughts on the coloring of the numbers?
Use random colors?
How many different initials are we talking about?
Can we asign a color to an initial or do the different kind of initials change over time?

Maybe it's best to share your workbook (without personal info) to a filesharing site like www.speedyshare.com or ge.tt. This way a more suitable solution can be found.

Best regards,
Trowa
Thanks everyone for the help!!

this is what I used:

IF(VLOOKUP(A5,$A$5:$A$9,COLUMNS($A$5:A$9),FALSE)="MD",C4+1,IF(VLOOKUP(A5,$A$5:$A$9,COLUMNS($A$5:A$9),FALSE)="BL",C4+1,IF(VLOOKUP(A5,$A$5:$A$9,COLUMNS($A$5:A$9),FALSE)="KP",C4+1)))

i then went and mass formated the rows to change color based on column A.. i am not sure how i got it but it is doing what i want it to for now..

Much appreciation!!
Blocked Profile
That is an excellent solution! I had no idea that you were looking for such an elegant solution! My apologies for not understanding the level of complexity in which the solution required, considering you so clearly communicated your needs! Congrats!