I know your colleague is now working on a solution, I still wanted to post what I have.
Hopefully he will have a way to deal with the inconsistencies in your data. For my code to work the following needs to be cleaned up:
Sheet Deliveroo Data, column A, Brand and Kitchen need to be separated with "-".
Brand and Kitchen data need to match up with sheet Order Details, columns V and U.
Once the clean up is done, the following code will do as requested:
Option Compare Text
Dim SF1, SF2, SF3, mString, lOrder As String
Dim mFind, mFind2, mFind3 As Range
Dim pTime, oTime, tSub, oPT As String
For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
SF1 = cell.Value
mString = cell.Offset(0, -1).Value
SF2 = Trim(Left(mString, InStr(mString, "-") - 1))
SF3 = Trim(Mid(mString, InStr(mString, "-") + 1))
With Sheets("Order Details")
Set mFind = .Columns("B:B").Find(what:=SF1, lookat:=xlWhole)
If mFind Is Nothing Then GoTo NextCell
firstaddress = mFind.Address
If .Cells(mFind.Row, "V").Value = SF2 And .Cells(mFind.Row, "U").Value = SF3 Then
lOrder = Sheets("Order Details").Cells(mFind.Row, "A").Value
Set mFind = .Columns("B:B").FindNext(mFind)
Loop While mFind.Address <> firstaddress
If lOrder = vbNullString Then GoTo NextCell
Set mFind2 = Sheets("Delivery Data").Columns("A:A").Find(what:=lOrder, lookat:=xlWhole)
Set mFind3 = Sheets("Data").Columns("A:A").Find(what:=lOrder, lookat:=xlWhole)
oTime = Sheets("Delivery Data").Cells(mFind2.Row, "C").Value
tSub = Sheets("Deliveroo Data").Cells(cell.Row, "E").Value
oPT = Sheets("Delivery Data").Cells(mFind2.Row, "L").Value * 100
pTime = Format(TimeSerial(0, 0, DateDiff("s", tSub, oTime) + oPT), "hh:mm:ss")
Sheets("Data").Cells(mFind3.Row, "L").Value = pTime
The code will error out, since sheet Deliveroo Data, cell A20 doesn't contain the "-" symbol. For the previous 18 cells, 6 matches were found (due to non matching Brand and Kitchen names), which can be found on sheet Data, column L.
Thanks for the query, it has been a good exercise for me!