Home

Methods to calculate gross and net flow between two locations

michaelweigel
New Contributor

Hello!

 

I have a table of about 10k rows that contains (among other data columns) two locations: Location A and Location B (see image).

 

Screenshot (19).png

 

I'm prepping this data for use within a mapping software, but have found that not only is having thousands of drawn lines a bit of a mess, there's probably a lot of movement between major cities that doesn't show up effectively due to all the lines being drawn on top of each other. To help condense this, I used concatenation to reduce repeat instances of travel patterns into a pivot table, as seen below.

 

Screenshot (20).png

 

I'd like to use Excel to calculate gross and net flows between two locations. To use New York and Los Angeles as examples, gross flow would be all rows that move between NY and LA, while net flow would be the absolute value of the difference in total instances between the two rows NY>LA and LA>NY. I'm not sure what sort of formulas to apply, but I'd love for an end result to look something like this but with duplicates removed and the full column filled in by an automated process (it's possible that the pivot table method I used might not be the best way to reach this result):

 

Screenshot (21).png

 

Any and all advice appreciated, thanks! Let me know if anything about this request is unclear and I'm more than happy to elaborate.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies