[RESOLVED] More Excel Macro Fun!

[The_Grudge]

Firstly, let me apologize for all of my "macro" posts the last few days. I could have merged them all into one but I had no idea that I was going to need more than one! If your out there DKenny you may be able to help since your solution yesterday is VERY similar to today's issue.

Okay, hopefully this is it.
I'm using Excel so I need a macro to do the following.

Lets' say I have the following data (Name, Patient ID, and Date)...

DAVID, 01251, 20051214
DAVID, 01251, 20051214
JOE, 05564, 20051215
ERIC, 06498, 20051215
ERIC, 06498, 20051216

Well, wherever the Patient and Date are the same I need to create an index.
So it would end up looking like...

DAVID, 01251, 20051214 ----> 1
DAVID, 01251, 20051214 ----> 2
JOE, 05564, 20051215 ----> 1
ERIC, 06498, 20051215 -----> 1
ERIC, 06498, 20051216 ------> 1

The index only increments if both the PATIENTID and NAME are the same.

Got me?

Thanks in advance for the help guys & gals.

[DKenny]

Well, wherever the Patient and Date are the same I need to create an index.
So it would end up looking like...

ERIC, 06498, 20051215 -----> 1
ERIC, 06498, 20051216 ------> 1

The index only increments if both the PATIENTID and NAME are the same.

Which is it? Patient and Date (per the first statement) or PATIENTID and NAME (per the second statement).

[The_Grudge]

Wow.....I need to SLOOOOOW DOWNNNNNNN.

It's patient ID and Date.

NOTE:
My file actually contains 15 columns or so but I figured it would be easier to cut out the 2 columns I need onto their own sheet, get the indexes with a macro, and paste the indexes back into the original sheet. That way I don't have to worry about messing with the other columns.

[DKenny]

OK
Assuming that you have moved the ID and Date columns to a new sheet, the following will index that sheet for you. You will need to change the reference for the rngDataTable variable.

Sub GrudgeIndex()
Dim rngDataTable As Range
Dim rngCell As Range
    
    'This is the 2 column range that contains the ID in
    'the first column and the date in the second column
    Set rngDataTable = ThisWorkbook.Worksheets(1).UsedRange
    
    'Note: I'm assuming that the range has already been sorted
    'by ID then Date - If not you should insert a sort here
    
    'Loop through each PatientID cell
    For Each rngCell In rngDataTable.Columns(1).Cells
        
        'I'm assuming that the 1st row contains headers
        If rngCell.Row <> 1 Then
            
            'If the ID is different that the ID on the
            'previous row, or the date is different from
            'the date on the previous row
            'we restart the index at 1
            If rngCell.Value <> rngCell.Offset(-1, 0) _
            Or rngCell.Offset(0, 1) <> rngCell.Offset(-1, 1) Then
                rngCell.Offset(0, 2).Value = 1
            
            'Otherwise we increment the index by 1
            Else
                rngCell.Offset(0, 2).Value = rngCell.Offset(-1, 2).Value + 1
            End If
        End If
    Next rngCell
    
    
    Set rngDataTable = Nothing
End Sub

[The_Grudge]

NICE.

How much easier is life now!!