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

 

[Shummy]

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.

Hi Peter,
Sincerely apologize for that as I only realized after running your macro that it picked up this scenario.
I'm not able to upload an excel sheet due to our company restrictions so I can only best explain with some examples. I will exclude the first 2 columns and show the difference combinations of Event Dates and Events

Event Date 1	Event 1	Event Date 2	Event 2	Event Date 3	Event 3	Event Date 4	Event 4	Event Date 5	Event 5
01/08/2022	Correspondence Sent	01/08/2022	Correspondence Sent	01/08/2022	Correspondence Sent	01/08/2022	Correspondence Sent	01/08/2022	Correspondence Sent
01/08/2022	Approval Request Submitted	02/08/2022	Correspondence Sent	01/08/2022	Approval Request Submitted	01/08/2022	Correspondence Sent	01/08/2022	Approval Request Submitted
		03/08/2022	Approval Request Submitted	02/08/2022	Correspondence Sent	02/08/2022	Approval Request Submitted	02/08/2022	Correspondence Sent
				03/08/2022	Approval Request Submitted	02/08/2022	Correspondence Sent	02/08/2022	Approval Request Submitted
						03/08/2022	Correspondence Sent	03/08/2022	Correspondence Sent
						04/08/2022	Approval Request Submitted	03/08/2022	Approval Request Submitted

The output would be similar to the above in which I will only need the first instance of each Correspondence Sent. Initially I requested for every time an Approval Request Submitted Event occurs, I would need the first instance of the Correspondence Sent. However, I have identified that there are scenarios in which Correspondence Sent could happen before Approval Request Submitted, so I would need to grab the first Correspondence Sent (Event 2 is an example.)

A quick question on the Dictionary Macro, my worksheet actually consists of many columns, I tried to change the code to fit my worksheet however I was receiving an error. Below is what I attempted. (Sorry am new to using Dictionary so was trying to attempt myself).

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
  '[Chris] I changed the range to N
  a = Range("A2", Range("N" & Rows.Count).End(xlUp)).Value
  'Set up an array plenty big enough to hold the results
  '[Chris[ I changed the array to 14
  ReDim b(1 To UBound(a), 1 To 14)
 ' 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.
    '[Chris] The column which the Event lies in in column L
    '[Chris] The column for Requisition ID and Candidate ID are in columns A and E
    If a(i, 12) = "Approval Request Submitted" Then
      d(a(i, 1) & "|" & a(i, 5)) = 1
    'Else if it is a Corresp row then ..
    ElseIf a(i, 12) = "Correspondence Sent" Then
      '.. IF that row's Req/Candidate combo is already in the dictioary
      If d.Exists(a(i, 1) & "|" & a(i, 5)) Then
        'Go to the next row in the results array ..
        k = k + 1
        ' .. write all the values into that results array row ..
        '[Chris] I am assuming here this I would change it based on the columns I require to in my destination worksheet
        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
        '[Chris] Just updated to delete the right record
        d.Remove (a(i, 1) & "|" & a(i, 5))
      End If
    End If
  Next i
  'Write the results array back into the worksheet
  '[Chris] An error was appearing here, was not sure exactly why
  Worksheet("Results").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(k, 4).Value = b
End Sub

Again, I really appreciate your time. This is the first time seeking help in Excel and you have been doing above and beyond for me. I cannot thank you enough.

Regards,
Chris

 

[Peter_SSs]

The output would be similar to the above ..

.. but not identical. That leaves me to guess what the output would actually be.

 

[Shummy]

.. but not identical. That leaves me to guess what the output would actually be.

Hey Peter,
Thanks for the reply. Identical in a sense that I would need the first occurrence of the Correspondence Sent regardless if its before or after the Approval Request Submitted.
Apologies for the confusion.

 

[Peter_SSs]

Apologies for the confusion.

Unfortunately, to my understanding, you are not resolving it.

As far as I can understand, since it seems the values can come pretty much in any order, there is no way to tell if the yellow highlighted row below is the first response after the 02/08/2022 Approval or the first response before the 03/08/2022 Approval (or the fifth response to some much earlier Approval)

Similarly, how do we tell if green row is the second response after 02/08 or the first response before 03/08?

If you could supple sample data and actual results (rather than described results) and clearly explain the logic in relation that then possibly (but not sure) I could get to understand the layout and requirement.

 

[Shummy]

Unfortunately, to my understanding, you are not resolving it.

As far as I can understand, since it seems the values can come pretty much in any order, there is no way to tell if the yellow highlighted row below is the first response after the 02/08/2022 Approval or the first response before the 03/08/2022 Approval (or the fifth response to some much earlier Approval)

Similarly, how do we tell if green row is the second response after 02/08 or the first response before 03/08?

If you could supple sample data and actual results (rather than described results) and clearly explain the logic in relation that then possibly (but not sure) I could get to understand the layout and requirement.

View attachment 71145

Hey Peter.
Thanks for your reply. For the above case it would be as how you have written your initial code as to pick up the first occurrence of the Correspondence Sent after the Approval Requested Event has happened. I assume this is Event 4, the thing different here is that there is no Approval Request event happening here for the first correspondence sent. Below is the expected result.

Correspondence Sent	01/08/2022
Correspondence Sent	02/08/2022

Let me see if I can somehow furnish my excel workbook to you. Will populate the scenarios on my personal laptop. Just wondering if there is a way to upload here as I don't see an option to, only uploading an image or mini sheet.
Again, really appreciate the time and commitment for helping.

 

[Peter_SSs]

Just wondering if there is a way to upload here

No, you cannot upload actual workbooks to the forum.