Search code examples
excelifs

How to name a specific sequence distinctivly in excel

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

Solution

  • 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