How can I compare data in a row to data in a previous row?

[shellj]

My spreadsheet contains information exported from a database. Column 1 contains a unique ID number but there may be multiple rows with the same number. In each row there are also dates - I want to be able to count the number of rows with the same ID number, take the last row of each number and compare the dates to the previous row in the same group. I'm sure I will have to use an array of some sort but am not entirely sure how to do this. Can someone please point me in the right direction. Thanks.

 

[ETID]

Have you tried a pivot table?

It's designed to do the type of thing you are describing.

 

[shellj]

I've used a pivot table but it's not really what I need. I probably didn't explain myself properly. If there is only 1 row with a certain ID, I need to perform a certain task on this row - if there are multiple rows (each of which contain dates when a specific action was performed), I need to calculate the difference between these dates and create a new date from this calculation. For example:

ID ACTION DATE

9998 HIR 1996-09-02
9998 TER 1996-12-02
9998 REH* 1997-03-20
9998 TER 1997-08-26
9998 REH* 1997-08-27
9997 HIR 1998-02-30
9997 TER 2000-03-02
9996 HIR 1996-04-01

I need to get the dates where the ID is the same AND the ACTION is REH and subtract the earlier date from the recent date. I hope this explains things a little better.

 

[Zathras]

Here is some code you may be able to adapt:

Option Explicit
Const FIRST_DATA_ROW = 2
Const COL_ID = 1
Const COL_ACTION = 2
Const COL_DATE = 3

Sub ProcessDates()
Dim nRow As Long
Dim nCounter As Integer
Dim nCurrentID As Integer
Dim nNextID As Integer
Dim sAction As String
Dim dtDate As Date
Dim dtLastHIR As Date
Dim dtLastREH As Date

  nCounter = 0
  nRow = FIRST_DATA_ROW
  While Cells(nRow, COL_ID) <> 0
    nCurrentID = Cells(nRow, COL_ID)
    nNextID = Cells(nRow + 1, COL_ID)
    sAction = Cells(nRow, COL_ACTION)
    dtDate = Cells(nRow, COL_DATE)
    nCounter = nCounter + 1

    ' If this is the last ID in the group, process it.

    If nNextID <> nCurrentID Then
       ProcessID nCurrentID, sAction, dtDate, _
                 nCounter, dtLastHIR, dtLastREH

      ' Reset current variables

      nCounter = 0
      dtLastHIR = 0
      dtLastREH = 0
    Else
      If sAction = "HIR" Then dtLastHIR = dtDate
      If sAction = "REH" Then dtLastREH = dtDate
    End If
    nRow = nRow + 1
  Wend
End Sub

Sub ProcessID(ID As Integer, Action As String, LastDate As Date, Count As Integer, _
                 LastHIRDate As Date, LastREHDate As Date)
Dim sMessageText As String
  sMessageText = "Count: " & Count & Chr(13) & Chr(10)
  sMessageText = sMessageText & "Action: " & Action & Chr(13) & Chr(10)
  sMessageText = sMessageText & "Date: " & LastDate & Chr(13) & Chr(10)
  If LastHIRDate > 0 Then
    sMessageText = sMessageText & "Last HIR: " & LastHIRDate & Chr(13) & Chr(10)
  End If
  If LastREHDate > 0 Then
    sMessageText = sMessageText & "Last REH: " & LastREHDate
  End If
  MsgBox sMessageText
End Sub

 

[shellj]

Thanks very much! I will try this and let you know.