Excel help - Supply Chain

[sk8r3124]

I don't usually run into roadblocks when it comes to excel, but am hoping someone here may be able to help with something I have been scratching my head on for a couple days. Would prefer to avoid a VBA solution.

essentially its a network diagram, where I have receipt and delivery points, and am trying to calculate the values of the arc between points along the system.

failed to handle it with a sumproduct, or an index/match and am hitting a roadblock.

here is an example of what I am hoping for:

http://photoshare.shaw.ca/view/28758326600-1450299982-60002/287583/

[pheoxs]

Is your picture correct? Because start at A, end at C should be 20, but yours says 5? Or is that just an example of what you want?

http://i.imgur.com/SCY4PGJ.png

Not pretty but it works.

Convert the letters to a segment number then use SUMIFS to check a range between two segments and add the values accordingly.

Edit: Noticed the last one goes from D to C, may just need tweak the sumifs formula to allow it to go backwards but thats minor

[sk8r3124]

Its an example of what I was looking for. A to B is 5, but adding in the second line of B to D makes the B to C 15.

Essentially I have 50+ Delivery/Receipt points, and 200+ rows to do.
If it were smaller I would just look on a matrix level, but there is a boat load of combinations.

[jwslam]

Originally posted by sk8r3124
Its an example of what I was looking for. A to B is 5, but adding in the second line of B to D makes the B to C 15
So confused... what are your knowns and what are you trying to get to??? :confused: :confused: :nut: :nut:

[sk8r3124]

I know the volumes, receipt point, and delivery point.
I am trying to determine at the end of the day, the volume that flows between each individual section. A-B, B-C, C-D, and so forth.

my output is essential a map to track the change in flow over a system

[realazy]

I can't see this being done except in an array. There's too many variables.

Unless you can parse the data like so:

A to D

Would have to show

A to B to C to D

This is also assuming it's linear.

Things moving backwards also throw a kink into this.

[UndrgroundRider]

Here you go: https://www.dropbox.com/s/cd5sm3vdu9vvidt/sk8r3124.xlsx?dl=0

Tricks:

- Convert letters to indexed numbers
- Use sumifs to sum on multiple criteria
- Break down link volume based on sub-components - 4 in this case. 1. units moving from a lower node to a higher node accross our link - excluding self originating traffic (ie. right direction through traffic), 2. units moving from a higher node to a lower node (ie. opposite of above - left direction traffic), 3. self originating traffic moving to a higer node, 4. higher node traffic moving to us

Build 4 sumifs statements using the above logic and you have your volumes. I tested it with random data and your supplied data, seems to work, but I have limited samples to test with.

[pheoxs]

Minor tweak then. This should work

http://i.imgur.com/P14W8TK.png

[sk8r3124]

Perfect, Thanks for all the help on this!