How to compare data in table and return sum of matching pairs?
Iíve got a table with three columns: the first column (column A) shows bus service numbers, the second (column B) indicates the route direction (circular, inward, outward) and the third column (column C) the respective mileage. I would like to add the mileage for matching inward and outward services so I know how long the return journey would be (the routes can be slightly different due to one-way streets for example). Here is a short example (my table contains a lot more services):
number direction mileage
3A Circular 3.18
5E Inward 9.47
5E Outward 9.43
5EB Outward 9.21
6A Circular 5.57
I think in a first step Iíd need to exclude all circular services by sorting the data, which is no problem. Then Iíd have to check if there is a matching pair in column A. Then, if there is such a pair Iíd need to check if one of them is an inward and the other one an outward journey in column B. If this is the case too, Iíd like to add up their individual mileages.
Any help on all steps would be greatly appreciated.
For the first step I tried MATCH but because the cellís value is within the range I am comparing it to, the result is always true (e.g. MATCH(A3,A$1:A$5,0)) and of course MATCH only returns the position of the matched value but I suppose I could overcome the latter by using an IF function and turning any number into TRUE?
For the other steps I donít have any ideas at all.
Maybe I am trying to achieve something that isnít possible but I donít hope so!
Many thanks for your help!
Assuming that your data as shown is in the range A1:C6 (including the headings) then
Copy down to D6.
Note there will be duplicates as the route will be shown twice. If you extracted a unique list of routes, put it somewhere else, you could reference that to get a single result for the total.
Thanks a lot! This is exactly what I was looking for!
Out of curiosity, could you tell me what exactly the SUMPRODUCT function is doing in this case and why it is set to equal 1? It seems to be doing something different to what is explained in MS Excel Help.
Thanks a lot! The link was very helpful!
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1