Return the first occurrence after criteria is met

[Shummy]

Hello.
Thanks for helping.
I've been cracking my brain for hours however I am still unable to figure out a way to write a formula. What I have initially done is identified the number of occurrences for "Approval Request Submitted" with this formula: =IF(D2="Approval Request Submitted",COUNTIF($D$2:D2,"Approval Request Submitted"),"") and tried to incorporate a formula to find the MIN Event Date whenever the next occurrence occurs however I have no idea how to incorporate this logic.

Requisition ID	Candidate ID	Event Date	Event
1	12345	16/02/2022	Approval Request Submitted
1	12345	17/02/2022	Correspondence Sent
1	12345	18/02/2022	Correspondence Sent
1	12345	19/02/2022	Correspondence Sent
1	12345	19/02/2022	Approval Request Submitted
1	12345	20/02/2022	Correspondence Sent
1	12345	21/02/2022	Correspondence Sent
1	12345	21/02/2022	Approval Request Submitted
1	12345	21/02/2022	Correspondence Sent

What I am trying to achieve is for each unique combination of Requisition ID and Candidate ID, I want to return the first occurrence of each Correspondence Sent after an Approval Request Submitted has occurred.

Requisition ID	Candidate ID	Event Date	Event
1	12345	17/02/2022	Correspondence Sent
1	12345	20/02/2022	Correspondence Sent
1	12345	21/02/2022	Correspondence Sent

Above is only 1 case. I assume I would need to also incorporate a MATCH/INDEX formula as I need to identify each unique Requisition ID and Candidate ID combination.

Appreciate everyone's help.

Thanks!

Regards,
Chris

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Shummy]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Appreciate that. Sorry missed it when I was creating my account.

 

[Peter_SSs]

Thanks for updating.

1. Is there any possible text in the 'Event' column other than the two shown in your sample?
2. Is it possible to have two 'Approval Request Submitted' lines with no 'Correspondence Sent' between them? If so, what sould happen with that circumstance?
3. In your sample, the line immediately after each 'Approval Request Submitted' line is 'Correspondence Sent'. Is that always the case?
4. Would a vba solution be acceptable if a formula is not possible, or is very complicated?

 

[Shummy]

Thanks for updating.

1. Is there any possible text in the 'Event' column other than the two shown in your sample?
2. Is it possible to have two 'Approval Request Submitted' lines with no 'Correspondence Sent' between them? If so, what sould happen with that circumstance?
3. In your sample, the line immediately after each 'Approval Request Submitted' line is 'Correspondence Sent'. Is that always the case?
4. Would a vba solution be acceptable if a formula is not possible, or is very complicated?

Hey Peter,
Thanks for the reply.
1. There are other events which can occur however I what I'm ideally doing is calculating the duration between the 2 dates between Approval and Correspondence.
2. It could be possible, if it does I would only acknowledge the first Approval Requested Submitted.
3. Not always the case.
4. I'm open to VBA.
Thanks and do let me know if you have any additional questions!

 

[Peter_SSs]

Thanks for those responses. Based on them, you could try this macro.

Sub Approval_Correspondence()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    If a(i, 4) = "Approval Request Submitted" Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
    ElseIf a(i, 4) = "Correspondence Sent" Then
      If d.Exists(a(i, 1) & "|" & a(i, 2)) Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3): b(k, 4) = a(i, 4)
        d.Remove (a(i, 1) & "|" & a(i, 2))
      End If
    End If
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, 4).Value = b
End Sub

Here are my sample data (rows 1:16) and results (rows 19:23)

Shummy.xlsm
	A	B	C	D
1	Requisition ID	Candidate ID	Event Date	Event
2	1	12345	16/02/2022	Approval Request Submitted
3	1	12345	17/02/2022	Correspondence Sent
4	1	12345	18/02/2022	Correspondence Sent
5	1	12345	19/02/2022	Correspondence Sent
6	1	12345	19/02/2022	Approval Request Submitted
7	1	12345	20/02/2022	Correspondence Sent
8	1	12345	21/02/2022	Correspondence Sent
9	1	12345	21/02/2022	Approval Request Submitted
10	1	12345	21/02/2022	Correspondence Sent
11	1	33333	16/02/2022	Approval Request Submitted
12	1	33333	17/02/2022	Other text
13	1	33333	18/02/2022	Correspondence Sent
14	2	22222	16/02/2022	Approval Request Submitted
15	2	22222	17/02/2022	Approval Request Submitted
16	2	22222	18/02/2022	Correspondence Sent
17
18
19	1	12345	17/02/2022	Correspondence Sent
20	1	12345	20/02/2022	Correspondence Sent
21	1	12345	21/02/2022	Correspondence Sent
22	1	33333	18/02/2022	Correspondence Sent
23	2	22222	18/02/2022	Correspondence Sent
Sheet1

 

[Shummy]

Thanks for those responses. Based on them, you could try this macro.

Sub Approval_Correspondence()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    If a(i, 4) = "Approval Request Submitted" Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
    ElseIf a(i, 4) = "Correspondence Sent" Then
      If d.Exists(a(i, 1) & "|" & a(i, 2)) Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3): b(k, 4) = a(i, 4)
        d.Remove (a(i, 1) & "|" & a(i, 2))
      End If
    End If
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, 4).Value = b
End Sub

Here are my sample data (rows 1:16) and results (rows 19:23)

Shummy.xlsm
	A	B	C	D
1	Requisition ID	Candidate ID	Event Date	Event
2	1	12345	16/02/2022	Approval Request Submitted
3	1	12345	17/02/2022	Correspondence Sent
4	1	12345	18/02/2022	Correspondence Sent
5	1	12345	19/02/2022	Correspondence Sent
6	1	12345	19/02/2022	Approval Request Submitted
7	1	12345	20/02/2022	Correspondence Sent
8	1	12345	21/02/2022	Correspondence Sent
9	1	12345	21/02/2022	Approval Request Submitted
10	1	12345	21/02/2022	Correspondence Sent
11	1	33333	16/02/2022	Approval Request Submitted
12	1	33333	17/02/2022	Other text
13	1	33333	18/02/2022	Correspondence Sent
14	2	22222	16/02/2022	Approval Request Submitted
15	2	22222	17/02/2022	Approval Request Submitted
16	2	22222	18/02/2022	Correspondence Sent
17
18
19	1	12345	17/02/2022	Correspondence Sent
20	1	12345	20/02/2022	Correspondence Sent
21	1	12345	21/02/2022	Correspondence Sent
22	1	33333	18/02/2022	Correspondence Sent
23	2	22222	18/02/2022	Correspondence Sent
Sheet1

Hi Peter,
Thanks so much. It works really well, do you mind if you break down the code and explain to me what's been written? Would like to learn!
Regards,
Chris

 

[Peter_SSs]

Thanks so much. It works really well,

You're welcome.

do you mind if you break down the code and explain to me what's been written? Would like to learn!

Sub Approval_Correspondence()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  'Set up a dictionary to record the unique Requisition/Candidate combos
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  'Read all the data into an array for faster processing
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  'Set up an array plenty big enough to hold the results
  ReDim b(1 To UBound(a), 1 To 4)
 ' Work through the rows of the data
  For i = 1 To UBound(a)
    'If it is an Approval row then record the Req/Candidate combo.
    'If it already exists in the dictionaru a new key is NOT recorded.
    If a(i, 4) = "Approval Request Submitted" Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
    'Else if it is a Corresp row then ..
    ElseIf a(i, 4) = "Correspondence Sent" Then
      '.. IF that row's Req/Candidate combo is already in the dictioary
      If d.Exists(a(i, 1) & "|" & a(i, 2)) Then
        'Go to the next row in the results array ..
        k = k + 1
        ' .. write all the values into that results array row ..
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3): b(k, 4) = a(i, 4)
        ' .. and delted that Req/Candidate key from the dictionary so that it can be added again if the combo arises again
        d.Remove (a(i, 1) & "|" & a(i, 2))
      End If
    End If
  Next i
  'Write the results array back into the worksheet
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, 4).Value = b
End Sub

 

[Shummy]

You're welcome.

Sub Approval_Correspondence()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  'Set up a dictionary to record the unique Requisition/Candidate combos
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  'Read all the data into an array for faster processing
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  'Set up an array plenty big enough to hold the results
  ReDim b(1 To UBound(a), 1 To 4)
 ' Work through the rows of the data
  For i = 1 To UBound(a)
    'If it is an Approval row then record the Req/Candidate combo.
    'If it already exists in the dictionaru a new key is NOT recorded.
    If a(i, 4) = "Approval Request Submitted" Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
    'Else if it is a Corresp row then ..
    ElseIf a(i, 4) = "Correspondence Sent" Then
      '.. IF that row's Req/Candidate combo is already in the dictioary
      If d.Exists(a(i, 1) & "|" & a(i, 2)) Then
        'Go to the next row in the results array ..
        k = k + 1
        ' .. write all the values into that results array row ..
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3): b(k, 4) = a(i, 4)
        ' .. and delted that Req/Candidate key from the dictionary so that it can be added again if the combo arises again
        d.Remove (a(i, 1) & "|" & a(i, 2))
      End If
    End If
  Next i
  'Write the results array back into the worksheet
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, 4).Value = b
End Sub

Ok that's actually really amazing I did not know VBA could be done in this way. Looks like I still have a lot more to learn.

Sorry Peter it seems there was one other condition which I missed when I ran your code. It seems there may be cases in which Correspondence Sent could be done before the Approval Request Submitted is done. For this I would need to also grab the first date in which the correspondence is sent. Below is a sample table.

Requisition ID	Candidate ID	Event Date	Event
1	12345	16/07/2022	Correspondence Sent
1	12345	17/07/2022	Correspondence Sent
1	12345	18/07/2022	Approval Request Submitted
1	12345	19/07/2022	Correspondence Sent
1	12345	20/07/2022	Correspondence Sent
1	12345	21/07/2022	Approval Request Submitted
1	12345	22/07/2022	Correspondence Sent

Apologies again that I missed this.

Thanks again for your help!

Regards,
Chris

 

[Peter_SSs]

It seems there may be cases in which Correspondence Sent could be done before the Approval Request Submitted is done.

That seems to make this a very different question to what was originally asked. I am now unsure of exactly what the 'logic' is of what is required.
For example, if "Correspondence Sent" appears somewhere between two rows with "Approval Request Submitted" I can't see how one would decide which "Approval Request Submitted" it related to.
To see if something was possible I would want to see a substantial set of sample data that had all the different variations in it, the expected results and explanation in relation to those results.