CODEWITHSUNDEEP
excel-formulaoffsetvlookup
Saud
Saud

Reputation: 490

Lookup function to define two directions for same dataset

Hi I want to create a pivot table basen on the data below. In column Q ("Ver 00") I have places/cities in abbreviations. I want to determine how many trips there are in both directions; from "bast" to "drg" and then from "drg" to bast". The problem is that all the trips do not always start in "bast" and finish in "drg" and the other way around. Sometimes they fx start in "nbst" and finish in "drg". I am completely lost here. But how do I make a column that recognizes which direction is "IN" and which direction is "OUT" if I define "bast" => "drg" as "IN" and "drg" => "bast" as "OUT"? I am thinking that vlookup might be useful here (defining the two directions) but I don't know if this will solve the problem.

The column should look like column AG.

I've tried with this formula but it is not working:

=IF([@Batch]<>N3,IF([@Rank]<=7,IF(COUNT.IF(Inrng,[@[Ver 10]]),"In",IF([@Rank]>7,IF(COUNT.IF(Outrng,[@[Ver 10]]),"Out",)))),AR3)

enter image description here

Upvotes: 0

Views: 122

Answers (1)

vacip
vacip

Reputation: 5416

Hmm, if you can be sure "bast" or "drg" is included in every batch (trip), then all you need is to determine which one was first in the given trip.

To do this, I'll use a match functions. These will have to find the "bast" and the "drg" of the actual batch. To do this, I'll concatenate the "Batch" and "Ver 00 "columns, and search in them.

Then all I need to do is compare the positions, see which one was first.

See below:

trips sample picture

Use this formula in cell D2:

=IF(MATCH(A2&"bast",C:C,0)<MATCH(A2&"drg",C:C,0),"in","out")

Is this closer to what you need? :)

Added new method for missing "bast" or "drg" cases:

Try this:

=IFERROR(IF(MATCH([@Batch]&"bast",[Batch&Ver 00],0)-MATCH([@Batch],[Batch],0)=0,"in","out"),IF(MATCH([@Batch]&"drg",[Batch&Ver 00],0)-MATCH([@Batch],[Batch],0)=0,"out","in"))

Note that I didn't do this in one go. I have used 5 helper columns, and did it step-by-step first. I recommend this method for complex calculations.

What I'm doing here:

Use a match to find the "bast" in the Batch.

MATCH([@Batch]&"bast",[Batch&Ver 00],0)

Use another match to find the first row of the actual Batch:

MATCH([@Batch],[Batch],0)

If these values are in the same row, then it is an "in", otherwise it is an "out":

IF(MATCH([@Batch]&"bast",[Batch&Ver 00],0)-MATCH([@Batch],[Batch],0)=0,"in","out")

Now, if this whole thing produces an error, that means "bast" can not be found. In this case, do the same search for "drg". This is what the iferror does:

IFERROR({...previous part...},IF(MATCH([@Batch]&"drg",[Batch&Ver 00],0)-MATCH([@Batch],[Batch],0)=0,"out","in"))

For reference, I here is another picture of the table I'm using, so that you understand the column names:

BastDrg table

Upvotes: 1

Related Questions