Count travels

[Mickey_124]

Hello,

i've got a simple excel, there are 4 columns and ~57000 rows:

ID	Vehicle	Start	Stop
1	Auto1	Paris	Madrid
2	Auto1	Madrid	Berlin
3	Auto2	Madrid	Paris
4	Auto2	Paris	Madrid
5	Auto3	Berlin	Madrid

1 row means 1 travel, for example in the first row Auto1 travels from Paris to Madrid.

I'd like to make a bar chart, where i'd like to count, how many Start-Stop relations i have. It doesn't matter if the Start and Stop location is reversed, if it's the same 2 location. In this little example, we would have bars with the following heights:

3 for Paris-Madrid bar (2 Paris-Madrid + 1 Madrid-Paris)
2 for Madrid-Berlin bar (1 Madrid-Berlin + 1 Berlin-Madrid)

I tried to solve this problem with a new master dimension:
if((Start='Paris' AND Stop='Madrid') OR (Start='Madrid' AND Stop='Paris'),'Paris-Madrid',(if....))
, but i have like 6-700 such relations, so it would be an endless expression.

Do you guys have any good idea?

Thank you in advance!

[Vegar · Accepted solution]

By using this script I easily get the table and chart below.

LOAD *,
RangeMinString(Start, Stop)&' - '& RangeMaxString (Start, Stop) as Travel
inline [
ID,Vehicle,Start,Stop
1,Auto1,Paris,Madrid
2,Auto1,Madrid,Berlin
3,Auto2,Madrid,Paris
4,Auto2,Paris,Madrid
5,Auto3,Berlin,Madrid];