Hello!
I have a table of about 10k rows that contains (among other data columns) two locations: Location A and Location B (see image).
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.
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):
Any and all advice appreciated, thanks! Let me know if anything about this request is unclear and I'm more than happy to elaborate.