CODEWITHSUNDEEP
excelvba
Michael
Michael

Reputation: 11

Date diff between many rows in Excel

I have USER_ID, EVENT_ID and DATE and I need to check which of the events are real.

Real event is the first one by each user and every one which is at least 10 days after the last real event.

What i do is to order them by USER_ID and date and using this forumla =IF(A2<>A1;1;IF(C2-C1>=10;1;0)) but that way it calculates date diff between current and the previous event, not to the last real event.

Like if i have events at 01 05 and 13 from one user it will give me 1, 0, 0 but i need to compare to the last real event which is at 01 and it should give me 1, 0 ,1.

How can i do that.

| USER ID  | EVENT ID  |    DATE      |  What I get   | What I should get |    
|:---------|:---------:|:------------:| :------------:|:------------:     |
| 1        |        4  |  01.01.2020  |       1       |       1           |
| 1        |        5  |  05.01.2020  |       0       |       0           |
| 1        |        6  |  13.01.2020  |       0       |       1           |
| 2        |        7  |  03.01.2020  |       1       |       1           |
| 2        |        8  |  05.01.2020  |       0       |       0           |
| 2        |        9  |  06.01.2020  |       0       |       0           |

The number of events by user is not fixed.

Table enter image description here

Upvotes: 1

Views: 105

Answers (1)

JMP
JMP

Reputation: 4467

Here's a solution in VBA.

Sub Button1_Click()
'data
Cells.Clear
[a2:b7] = [{1,1;1,5;1,13;2,3;2,5;2,6}]
'code
lre = [b2]
For i = 2 To 7
  If Cells(i, 1) <> Cells(i - 1, 1) Then
    Cells(i, 3) = 1
  ElseIf Cells(i, 2) - lre >= 10 Then
    Cells(i, 3) = 1
    lre = Cells(i, 2)
  Else
    Cells(i, 3) = 0
  End If
Next i
End Sub

It uses the same logic as your formula, but uses the lre variable to store the last real event's date.

output

Upvotes: 1

Related Questions