Merging Flow Data

[runway9r]

Some colleagues and I can't quite get this one.

We have some travel flow data between places such as:

A B 40
B C 50
F Y 9
B A 2
T R 65
Y F 33

Each of the end points (A, B, etc) is in a column, and then the third column is the number of people that moved between those points. What we need to to is combine the flows so that we can total them between points without regard to direction (i.e. we want A B = 42). In the end we want one list with the unique endpoints and the flows (and it doesn't matter which endpoint comes first). Any ideas? It seems like this should be easy - and maybe it is - but we can't get the answer (note - there are about 5,000 lines of data so it is tough to do by hand).

 

[CWatts]

If you can, make a helper column in column D and use the following formula:

=A2&B2

After you have that formula pasted down, you'll just put AB in a cell (I'll use F2) and then use the following formula:

=SUMIF(D:D,LEFT(F2,1)&RIGHT(F2,1),C:C)+SUMIF(D:D,RIGHT(F2,1)&LEFT(F2,1),C:C)

That will sum the movement from A to B and the movement from B to A.

That work for you?

 

[runway9r]

Thanks CWatts -- I am going to have a go at this today and let you know ! Appreciate the help.

 

[runway9r]

That work for you?

Brilliant. Thank you.

 

[runway9r]

Update and another question ...

After you have that formula pasted down, you'll just put AB in a cell (I'll use F2) and then use the following formula:

=SUMIF(D:D,LEFT(F2,1)&RIGHT(F2,1),C:C)+SUMIF(D:D,RIGHT(F2,1)&LEFT(F2,1),C:C)

That will sum the movement from A to B and the movement from B to A.

That work for you?

This method definitely works. The part above where I put AB in cell F(x) means that I have to know a priori the unique combinations of start points and end points from my master list. Is there any way if I have a list of thousands of start and end points and flow points to pull out those unique combinations of origins and destinations for column F (using your column designation above)? That is, if I have

A B 40 (say in row 1) and then
B A 75 (say in row 400) ..... and lots of data in between ....... I don't want to list both AB and BA in row F because I will be double counting (that is, when I use the formula, both AB and BA will = 115. I just want the 115 counted once, not twice. I need to filter somehow and pull out the AB and ignore the BA for F column (obviously I will use the entire data set for the actual addition part).

By the way, some of my data also might be:
X Y 24 ...... but there is not a corresponding Y X .... it just goes one way.

Thanks for any suggestions

 

[shg]

You could catenate the origin and destination in alphabetical order to create a trip name, and filter that for unique entries:

       -A-- -B-- ---C---
   1   Orig Dest  Trip  
   2   BOS  DFW  BOS|DFW
   3   BOS  HOU  BOS|HOU
   4   BOS  SJC  BOS|SJC
   5   BOS  CLE  BOS|CLE
   6   BOS  MCO  BOS|MCO
   7   CLE  DFW  CLE|DFW
   8   CLE  HOU  CLE|HOU
   9   CLE  SJC  CLE|SJC
  10   CLE  BOS  BOS|CLE
  11   CLE  MCO  CLE|MCO
  12   DFW  HOU  DFW|HOU
  13   DFW  SJC  DFW|SJC
  14   DFW  BOS  BOS|DFW
  15   DFW  CLE  CLE|DFW
  16   DFW  MCO  DFW|MCO
  17   HOU  DFW  DFW|HOU
  18   HOU  SJC  HOU|SJC
  19   HOU  BOS  BOS|HOU
  20   HOU  CLE  CLE|HOU
  21   HOU  MCO  HOU|MCO
  22   MCO  DFW  DFW|MCO
  23   MCO  HOU  HOU|MCO
  24   MCO  SJC  MCO|SJC
  25   MCO  BOS  BOS|MCO
  26   MCO  CLE  CLE|MCO
  27   SJC  DFW  DFW|SJC
  28   SJC  HOU  HOU|SJC
  29   SJC  BOS  BOS|SJC
  30   SJC  CLE  CLE|SJC
  31   SJC  MCO  MCO|SJC

The formula in C2 and down is

=IF(A2 < B2, A2 & "|" & B2, B2 & "|" & A2)

 

[CWatts]

Well, let me give you a little background on the formula and maybe you can suit it to your needs since you're much more familiar with your data.

You first wanted data to and from two points:

=SUMIF(D:D,LEFT(F2,1)&RIGHT(F2,1),C:C)
+SUMIF(D:D,RIGHT(F2,1)&LEFT(F2,1),C:C)

Basically the two parts of the formula are duplicates of each other, the only difference is that the first one takes the left character first and the right character second and the second one takes the right character first and the left character second, effectively reversing the direction.

If you only one one-way travel, then just use the first part.

Are you looking at this to just see something and go from there, or is this going to be an ever expanding always evolving project?

 

[runway9r]

Thanks shg and CWatts. Actually, the type of air travel flow data shg is exactly what I am looking at. I am using the flow data as inputs into other calculations. The data are by year, so I would just be applying the method for each year of data that I have. Once I do the calcs for a year, that's it. I move on to the next.

I think the combination of your two suggestions will work and will fire it up. CWatts - your formula definitely works -- it was just getting the list. Shg from what I can see your suggestion should get me there. Will update.

 

[runway9r]

Thanks shg and CWatts. Will update.

Just wanted to follow up: the combination of your two suggestions indeed works perfectly. Shg's method for coming up with the unique list efficiently does the job and CWatts' equation for making the bi-directional flow calc is slam-dunk. I just was able to employ it on a 5,000 line data set and do some testing to confirm that all was correct. Again, thank you both for your innovative solutions to the problem. r9r.