1

I have a DB which contains shipping orders for different clients, one shipment can have more than one dispatch with different estimated arrival times, the logic is: dispatches on each route must follow a sequential order (06:00->06:30->08:00) and not (06:30->08:00->06:00) all times are in the same column but the route number is on another column. Validation must be done only for dispatches in the same route.

First I tried using a separate sheet with cells calculating the difference between each time and the next in the route but I get errors when the formula encounters blank cells. The formula was:

=IF(COUNTIFS($I$2:$I$2000;$I2;$A$2:$A$2000;$A$2)>1;$E3-$E2;0)

I used COUNTIFS() to validate the dispatch wan on the same route for the same dispatch center, since the DB consolidates 5 dispatch centers. The formula returned negative numbers when it detected an incorrect sequence, but it was detectting false positives between the last and first dispatches on adjacent routes.

I then tried to use conditional formatting to check this, using:

=IF(COUNTIFS($I$2:$I$2000;I2;$A$2:$A$2000;A2)>1;E3<E2;0)

However, something is not right... I would appreciate if someone could give me an insight to make this work, or if there is another way to do this validation (I am sure there is one, but I'm not yet aware of it). Please help.

UPDATE: Here is some sample data, as you can see the are times that are not sequential, but also false positives on last/first dispatches on each route

Sample data clarified, and it is showing the expected result, need to avoid the false positives. please help

Diego Rojas
  • 13
  • 3
  • I just added the sample data – Diego Rojas Oct 21 '19 at 01:51
  • I updated the sample data, and like you said, I am kind of showing the expected result, the conditional formatting currently being used does show the times which aren't sequential, nut is also showing false positives on last/first dispatches on different routes.and I'm not able to exclude that case yet. – Diego Rojas Oct 21 '19 at 02:52
  • Data shown is from sales department, and logistics needs to automate the revision of planned routes. Product is sent to the clients regardless of delivery order but we need to cuantify the frequency in which sales gives us a badly programmed route to help us and them to correct the mistakes via this feedback. It is s lot of pain to check the correct sequential delivery order manually because there are a lot of deliveries and this report needs to be ideally automatic with minimal revision. – Diego Rojas Oct 21 '19 at 03:09
  • Each route is exactly 300 units and my logic was to identify the sequence by number signs (+/-) is the value from the substraction was negative it indicated that the order was not correct, however I know this might not be correct since it feel it's a of s forced solution, if you k ow any other way I could achieve to validate the delivery order I'm open to suggestions – Diego Rojas Oct 21 '19 at 03:12
  • so Column I is actually route number given that each route is 300 units. I think now I understand what you tried to do. You want to find out for each route, if all deliveries are taking place in the ideal time order `06:00->06:30->08:00`. If one delivery is taking place at a later time but positioned in front of other deliveries under same route (column I) and same product (column B), you want to highlight it. – Terry W Oct 21 '19 at 03:18
  • Yeap exactly, that's what k wanted to do, I'm sorry if couldn't explain it correctly, I have another sheet which is almost a copy of this one, but instead of the original times it has 1 when this incorrect programming happens and 0 for when everything is correct I then sum all ones as counting occurrences and graphing the resume. Right now I was trying to do that by highlight the errors but if I can cuantify occurrences that might be the same result I think... I just want to know if this is the correct approach or if there is a better way to do this – Diego Rojas Oct 21 '19 at 03:37
  • will the information in column B `Entrega` and F `Cliente` play a role in this validation process? – Terry W Oct 21 '19 at 03:41
  • Nop, column B is the unique identifier for each delivery so it never repeats, and column F is the client getting the product – Diego Rojas Oct 21 '19 at 03:48

1 Answers1

1

Suppose you have the following named ranges:

  • Col_A being your data in Column A;
  • Col_E being your data in Column E;
  • Col_I being your data in Column I.

You can use the following formula as the formatting rule:

=NOT(AGGREGATE(15,6,Col_E/(Col_A&Col_I=$A2&$I2),COUNTIFS($A$2:$A2,$A2,$I$2:$I2,$I2))=$E2)

Highlight Column E with cell E2 being the active cell, go to conditional formatting to set up the above formatting rule, and you should have something like the following:

Example

Column J is for demonstration only. You do not need this column in your actual worksheet.

The logic is to use Col_E/(Col_A&Col_I=$A2&$I2) to return the relevant range of time from Column E based on information in Column A and I, then use AGGREGATE function to return the nth smallest value from this range where nth is determined by the COUNTIFS formula, then compare the nth time with the actual time to find out if they are the same. If not that means a delivery was scheduled at a later time but positioned ahead of other deliveries that was scheduled at an earlier time, or vice versa (that's why both the ones that were delivered early with a later time and the ones that were delivered late with an earlier time are both highlighted in my example).

Let me know if you have any questions. Cheers :)

Terry W
  • 3,199
  • 2
  • 8
  • 24