Adding 1 to an existing number if criteria is matched?

Solved/Closed
rshelton22 Posts 2 Registration date Tuesday June 4, 2013 Status Member Last seen June 5, 2013 - Jun 4, 2013 at 02:35 PM
 Blocked Profile - Jun 8, 2013 at 06:56 PM
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!!
Related:

4 responses

rshelton22 Posts 2 Registration date Tuesday June 4, 2013 Status Member Last seen June 5, 2013
Jun 5, 2013 at 11:54 AM
Could use some help.. pulling my hair out.. =(
0
Blocked Profile
Jun 5, 2013 at 12:16 PM
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 6, 2013 at 11:17 AM
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
0
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!!
0
Blocked Profile
Jun 8, 2013 at 06:56 PM
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!
0