0

I am having the following trouble. I have a set of data in excel which represents the route a bus has taken. Based on the route that the bus has taken, i want to determine if it is Route 1 or Route 2.

The way that I can understand if the bus has been performing Route 1 or Route 2, is only by the numbering of specific stops. For example, the bus has the same stops up to a certain point, number from 1 to 5. This means stops 1 to 5 are the same for both routes. Where they begin to distinct are the consecutive stops. Route 1, after stop 5, has the stops numbered 10 and above ( for example 1,2,3,4,5,10,11,12,12,11,10,5,4,3,2,`) which concludes a full cycle. Same thing for Route 2, which after stop 5 is sequenced 6-9 (1,2,3,4,5,6,7,8,9,9,8,7,6,5,4,3,2,1). What i want to achieve here is that in my table with information, for each bus stop event to determine if this stop event is done as part of route 1 or part of route 2. Below an example of how my data looks like and what i want to achieve. In yellow is what i want to achieve. Without color is the data i already have.

I tried an ifs formula which doesnt work.

Example of what i want to achieve

Barsino Larushku
  • 3
  • 1
  • Can you show us the `IFS()` formula you tried? – Dominique Jul 02 '21 at 08:30
  • @Dominique tried using =IF(NUMBERVALUE(A2)<10,IF(C3=2,2,1),2) but the problem is that this numbers the wrong stops as route 2. C is the column i colored in yellow in example above. I also tried =IFS(AND(NUMBERVALUE(A2)<10,C3<>1),2,AND(NUMBERVALUE(A2)<10,C3=1),1,NUMBERVALUE(A2)>=10,1) which still doesnt work – Barsino Larushku Jul 02 '21 at 08:48

1 Answers1

0

This seems to work on your example.

Essentially I look at the line with "001" if "010" comes five rows below it's "1", else "2".
And it just copies the row above if it's not the start of a route.

In C2:

=IF(A2="001",IF(A3="002",IF(A7="010",1,2),C1),C1)

and drag down.

enter image description here

EDIT to match request of inconsistent data.

In C2 add this formula:

=IF(B2="Outbound",IF(ISNUMBER(MATCH("010",A3:A9,0)),1,2),C1)

It now looks if it's outbound, if not grab above value.
If it is outbound then search for "010" in the six rows below, if there is a match then it's "1", else "2". enter image description here

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • This is perfect, but sometimes the sequence of the bus stop does not start from 001 or 002, because the data is generated by a GPS tracker and sometimes the bus stop event is missed, thus the first stop of the cycle is not 001, but say 003. – Barsino Larushku Jul 02 '21 at 09:02
  • I'm quite sure that can be fixed. But what is the preferred outcome? That you only get this part corrected, or that you entire data gets updated to include "001" and "002"? The latter needs VBA. – Andreas Jul 02 '21 at 09:33
  • No i dont want that i change the actual data to include 001 and 002 etc.. I need the data to be as it is, i would only like to adjust the formula (if possible) to take in consideration that the data is not uniform. – Barsino Larushku Jul 02 '21 at 09:40
  • That worked really well. Thanks a lot for your time and effort :) I really appreciate it! – Barsino Larushku Jul 02 '21 at 13:03
  • No problem. You can accept the answer to make it visible that the question has been solved by clicking on the tick mark next to the answer above – Andreas Jul 02 '21 at 13:14