Reverse match in Excel
I've got a lot of data like the following, where each row is a log entry:
A B C D E | Date | Truck Number | Arrival time | Departure time | More columns here... |------------+--------------+--------------+----------------|
1 | 2019-04-08 | 233 | 12:32 | 12:45 |
2 | 2019-04-08 | 245 | 12:56 | 13:03 |
3 | 2019-04-08 | 233 | 13:24 | 13:32 | <-- Searching relative to this one
4 | 2019-04-08 | 4221 | 13:40 | 13:48 |
5 | 2019-04-08 | 245 | 13:51 | 13:57 |
6 | 2019-04-08 | 233 | 14:08 | 14:23 |I need to include in each row a calculation based off of when the truck in the row arrived the next time, and when it arrived the previous time. I'm using helper columns to store the offset from the current row.
My examples are all relative to truck 233 which arrived at 13:24 (row 3). It's next arrival is three rows later, so the helper column should contain 3. I'm able to accomplish this with the following formula:
=IFERROR(MATCH([@[Truck Number]],$B4:$B$6,0),"")However, I also need to get the row of the previous visit of the same truck. So, in this example, I need to find row 1, or the row offset, 2, and put that number in the helper column.
How can I search backwards? I find it inexplicable that it's so hard to reverse a simple backwards search.
What I've tried so far
Based on several things I've found online, I tried this:
=SMALL(IF($C$3:$C10=[@[Truck Number]],ROW($C$3:$C10),""),1)But it returns the first element of an array, and there doesn't seem to be any way to get the last element without already knowing the size of the array.
12 Answers
The AGGREGATE function, along with judicious creation of DIV/0 errors (which AGGREGATE can ignore) is your friend
I have assumed that you really want the previous/next arrival times and not the offset from the current row to that time. If that is not the case, and you really do want the offset number, or row number, the formula changes are minor, but it would help to know exactly what you want to do, in that case.
Previous Arrival:
=IFERROR(AGGREGATE(14,6,1/(([@[Truck Number]]=[Truck Number])*([@[Arrival time]]>[Arrival time]))*[Arrival time],1),"")Next Arrival:
=IFERROR(AGGREGATE(15,6,1/(([@[Truck Number]]=[Truck Number])*([@[Arrival time]]<[Arrival time]))*[Arrival time],1),"")Use the formula evaluation tool to help understand how this works.
We create arrays and the only matches for the previous arrival times will be non-error values. The largest of those values will be the previous time.
Similar logic for the next arrival time, except the next one following will be the smallest (non-error) value.
7Going to look at the AGGREGATE function. It will do the same as your ARRAY formula above without being an array. AGGREGATE takes the following general format
AGGREGATE(Formula Number, Option Number, range/array, parameter)Formula 14 and 15 perform LARGE and SMALL functions and make array calculations within the AGGREGATE function. As a result do not use full column/row references within the AGGREGATE function or you can wind up with excessive calculations. This in turn can bog down your system. So the formula so far will look like:
AGGREGATE(15,The option number you can read for yourself as you are typing it in, I choose 6 to make it ignore errors which is important to the process. So the formula becomes:
AGGREGATE(15,6,So the next part is building the list of what you want. You are going to be interested in find the row number where you specified truck is for starters. We are going to do this by dividing the row number of the array calculation but a TRUE or a FALSE. The important thing to note here is that excel converts TRUE to 1 and FALSE to 0 when sent through a math operation. So the array calculation will look something like:
ROW(B1:B6)/(B1:B6=233)
or if your truck number is stored as text
ROW(B1:B6)/(B1:B6="233")So what happens is whenever the row being looked at does not match with 233, the divisor becomes FALSE. Then the row number is divided by FALSE which converts FALSE to 0. This means you get a divide by 0 error. So if we toss that into the formula so far, the 6 option will ignore all those errors only leaving a list of row numbers where a match occurred. So the formula so far now looks like:
AGGREGATE(15,6,ROW(B1:B6)/(B1:B6=233),So now you are left with the parameter option. Normally I am using this process to grab the first item in the list so I set it to 1. If I am using it to generate a list, I will set it to ROW(A1) and copy the formula downward so I get a list 1, 2, 3, etc. In your case, you are going to need to know a few things. 1st is what occurance of your truck are you. Is it the first one in the list? last one in the list? the only one is the list? you can get which truck in the list you are using a COUNTIF function once you find out what your position in the acceptable list is, you can subtract 1 from it to get the previous truck row, and you can add 1 to it to get the next truck row. The COUNTIF formula will look like:
COUNTIF(The range to count in, what it is that is being counted)So the formula for your case would be:
COUNTIF($B$1:B1,B1)As that formula gets pulled down, notice that the end of the range will increase, but the start will stay put. This can now be placed in the AGGREGATE formula. To get the previous truck you will want:
AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1)For next truck you will want:
AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1)yeah that great that I got the row but you asked for the row difference. HOWEVER THANKS TO AN UPDATE YOU REALLY WANT THE TIME!
I would just embed this in an INDEX function and pull the information I was looking for based on the row number. INDEX works in a couple of difference ways. If its a single line in a column or row, its 1D and if it covers multiple rows and columns its 2D. For 1D you only need to state how fall in the list you want to look. In 2D you need to tell it which row AND which column to look in. So two interesting tid bits. If you put 0 in for row or column reference then it looks at the entire row or column instead of a specific one. The other tid bit of info is the INDEX really returns a reference range/cell, not the actual contents of the cell directly. It will return arbitrarily F4 and then the F4 will pull the contents of cell F4. It allows you to do things like INDEX(...):INDEX(...) where the indexes could be used to define the start and end of a range for another formula. So INDEX takes the form of:
1D
INDEX(1D range to look in, how far into the list to look)
2D
INDEX(2D range to look in, what row to look at, what column to look at)
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1))the next arrival time would be:
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1))and in the case of being the first truck/only truck you could just return "" or "First Truck" by using an IFERROR function like so:
=IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1)),"First Truck")You could do the same thing for if the last truck. Alternatively you could throw up some text stating LAST TRUCK like so:
=IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1)),"Last Truck")The example below included the header row so reference ranges are off by 1 row from text above. Adjust reference to suit your needs.
Thanks to Ron Rosenfeld's answer which does a nice time comparison instead of counting, I notice that this method of counting only works if the trucks are sorted chronologically.
2
