Adding 1 to an existing number if criteria is matched? Solved/Closed

Question

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!!

rshelton22 · Answer

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

ac3mark · Answer

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.

TrowaD · Answer

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

rshelton22 · Answer

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!!

Adding 1 to an existing number if criteria is matched?

4 responses

Similar discussions

Subscribe To Our Newsletter!