Add Time Unless Duplicate Value

[kingswoodos]

Hi,

We have a spreadsheet we use to route our drivers deliveries, however as this is derived from a 3rd party piece of software, it does not work exactly as we want. Basically, the drivers get 4 minutes to do each delivery, however if the same customer has 2 orders, it gives them 8 minutes, which we do not want it to do.

What I want to do is change the setting on the 3rd party software to give the driver 0 minutes at each delivery, and have our spreadsheet add 4 minutes itself, unless the customer name is the same as the one in the row above. I cannot find a way of doing this, can anybody help please?

 

[barry houdini]

Hello kingswoodos, welcome to MrExcel

Lets assume you have start time in B1 and customers listed in A2 down

Try using this formula in B2 copied down to give the new times

=B1+IF(A2=A1,0,"0:04")

 

[kingswoodos]

Hello kingswoodos, welcome to MrExcel

Lets assume you have start time in B1 and customers listed in A2 down

Try using this formula in B2 copied down to give the new times

=B1+IF(A2=A1,0,"0:04")

Hi there,

That would work, however our routing software calculates travel time. Here is a rough idea of how it is now on this link http://www.kingswoodos.co.uk/EXCEL.JPG

In this example 4 minutes are added even though it's the same customer - this calculation comes from our routing software. However I was looking at adding another column which would deduct 4 minutes if the customer name and postcode are the same.

 

[barry houdini]

OK, perhaps easier to have a "helper column" to identify duplicates, e.g. in E5 copied down try this formula

=IF(AND(B4=B5,C4=C5),"Dup","")

....now in column F get the revised time by subtracting 4 minutes for every duplicate, i.e. with this formula in F4 copied down

=MOD(D4-COUNTIF(E$4:E4,"Dup")*"0:04",1)

If required hide columns D and E