Is there a formula where it can block the time in B1 to where it sees that it's during the 10:00AM block and look at column C and D and see if there is a change in number within that 10:00AM block but only when the location in column A is the same. So for example we see that in location AAA there was no number on 6/1/2015 at 10:10 but there was one at 10:16. But there was one number at 10:10 on 7/1/2015 so this would meaning that they kept 1 in that 10:00 block and would return "Kept 10:00 block". The formula would then continue to go down and see the next time is 13:53 (meaning it's in the 13:00 block) and look to see that there is no previous 13:00 block number so it would return "Kept 13:00 block" And then move further down and see that there currently is a 15:00 block number in 7/1/2015 but no 15:00 number in 6/1/2015 which would then return "Gained 15:00". And would continue to move down the line recognizing when the Location in column A would change.
Ideally it would then look something like this.
A | B | C | D |
Location | Time | 7/1/2015 | 6/1/2015 |
AAA | 10:10 | 1 | |
AAA | 10:16 | 1 | |
AAA | 13:53 | 1 | |
AAA | 15:37 | 1 | |
AAA | 16:25 | 1 | |
AAA | 17:35 | 1 | |
AAA | 21:35 | 1 | |
AAA | 21:44 | 1 | |
BBB | 14:03 | 1 | |
BBB | 14:13 | 1 |
Ideally it would then look something like this.
AAA | Kept 10:00 |
AAA | Kept 13:00 |
AAA | Gained 15:00 |
AAA | Lost 16:00 |
AAA | Gained 17:00 |
AAA | Kept 21:00 |
BBB | Kept 14:00 |