Punching Time [Solved]

Report
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020
-
luigidelmonte
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020
-
Hi again!

I have this task ofcalculating the punching time of our agents. I have 4 sheets in one excel.
"Deliveroo Data" This is where the orders are coming from, "Order Details" and "Delivery Data" tabs are coming from our system, and "Data" where we would put in all the details needed . So basically, in order to get the punch time, We'll have to put the "time submitted" from the "deliveroo data" tab and the "order time" from the "Delivery data" tab in the "DATA" tab and subtract it and add the "order processing time" from the "delivery data" tab to get the PUNCHING TIME. I hope I was clear with my query, if you need more clarification please inform me. I will attach the excel file as well Im not sure how to upload ithere but i will send it via email

11 replies

Posts
2598
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 10, 2020
401
Hi Luigi,

Unless I'm missing something, this could be done with 3 times the VLOOKUP function in 1 formula.

If you have trouble applying it, then having a look at your file would definitely help. You can do so using a free file sharing site like http://www.ge.tt. Upload your file (always be carefull with sensitive data) and post back the download link here.

Best regards,
Trowa
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3531 users have said thank you to us this month

luigidelmonte
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

The problem is that I can't use VLookup unless they have the same Brand name and the name of the branch (I think that's how it works) but in this case, these data are coming from different sources and the name is not matching up so I can't get vlookup working for this or maybe if there's a way to match the "restaurant name" from deliveroo data and the "brand" in data tab.

Here is the file: http://www.ge.tt/2aUQwT03

(To be clear on what i'm trying to do, I need to count the punching time. "Time Submitted" in deliveroo data tab is the time that the portal sent us the order, "Order time" from delivery data tab is the time when the agent is done punching the order and "Order Processing Time" from the same tab is the exact minutes it took the agent punching it or how much time is the system open while the agent is punching the order)
So Order Time - Time Submitted + Order Processing Time is what im trying to do but im on a roadblock here.
Posts
2598
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 10, 2020
401
Hi Luigi,

You make it like a real puzzle. I couldn't find the location, but it seems that the Restaurant Name also contains the location.

So Restaurant Name on Deliveroo sheet = Kitchen and Brand columns on the Order Details sheet.

For someone not familiar with your data, it is quite confusing.

I'll get back to you.

Best regards,
Trowa

1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3531 users have said thank you to us this month


Oh, it is true! "No good Deed goes unpunished!"
luigidelmonte
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

Hi TrowaD!

how's your day going? Don't worry about it, it's now being done by my colleague who has knowledge of using jupyter notebook which I have no idea on how to use it haha.

Anyways, thank you so much for always trying to help. You've been great and a life saver.

Talk to you soon!!

Regards,
Luigi
Posts
2598
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 10, 2020
401
Hi Luigi,

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
Sub RunMe()
Dim SF1, SF2, SF3, mString, lOrder As String
Dim mFind, mFind2, mFind3 As Range
Dim pTime, oTime, tSub, oPT As String

Sheets("Deliveroo Data").Select

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
    
    Do
        If .Cells(mFind.Row, "V").Value = SF2 And .Cells(mFind.Row, "U").Value = SF3 Then
            lOrder = Sheets("Order Details").Cells(mFind.Row, "A").Value
        End If
        Set mFind = .Columns("B:B").FindNext(mFind)
    Loop While mFind.Address <> firstaddress
    
    End With
    
    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
    
NextCell:
Next cell
End Sub


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!

Best regards,
Trowa
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3531 users have said thank you to us this month

luigidelmonte
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

Thannk you so much Sir TrowaD! I'll be posting more queries next time. Thank you very much and I mean it from the bottom of my heart!

Regards,
Luigi
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

Please help me with this anyone?
Posts
2598
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 10, 2020
401
Hi Luigi,

I'm unable to download your file, not sure why. I've uploaded files myself to test, but those files I can download just fine. Maybe it's the file size, maybe the site, maybe combination.

Can you decrease the file size by deleting tabs that are irrelevant to your query?

Sorry about this.

Best regards,
Trowa
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

Posts
2598
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 10, 2020
401
Hi Luigi,

Great that worked!

Now I have 2 questions:

1. What connects the rows from the Deliveroo Data tab to the rows of the Delivery Data tab? I'm looking for a unique value on both sheets, so I know which rows of data go together. Like the order number for example, but Deliveroo order number is like ####, while the Delivery order number is like ###-#####.

2. Your order processing time isn't a time but a number. How do I need to interpret that number? When i says 0,88, does that mean the processing time is 0,88*60 = 53 minutes?

Best regards,
Trowa
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

Hi Trowad,

Sorry for the delayed response. I was handling too many reports.

to answer your questions:

1. The order number from deliveroo data tab ####, you can connect it with the order details tab and then you can match the order number ###-#####from the order details tab to the delivery data tab. That's how you can match them. The problem is, there are multiple items with the same order number in deliveroo data tab.

2. with the order processing time, if for example it's 0.88 it means it was punched 88 seconds.
Posts
2598
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 10, 2020
401
Hi Luigi,

No problem, we both have other things to do :).

1. There needs to be a way to link the rows together. Which data need to be looked at (with the least amount of data as possible) to indentify each row as unique?
For example: When I look at order 4439, there are 3 of them, but they all have different restaurants. Linking the short order number to the restaurant would make it unique, to find that rows long order number to be found on the Delivery tab.
But I'm not sure if that is the case for all order numbers.

2. Ok.

3. Where (Sheet and column) do you actually want the punching time to be placed?

Best regards,
Trowa
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

Thanks Trowad,

1. The Order ID, restaurant and the location/branch would make it match.

We can place the punching time on the "data" tab.

What column in the data sheet?
Posts
18
Registration date
Sunday August 18, 2019
Status
Member
Last seen
January 24, 2020

we should add a new column on the last part