I have a dataset where an amount can move forward any number of columns, backwards any number of columns, can be removed or added.
I want to find the balance only, of those that have moved forwards and backwards, disregarding those who have been added or removed. For example in row f below that although a 100 balance was added to column 4, I only want the balance of the 300 that moved from column 3 to column 2.
Current Week:
Last Week:
For some reason the second table just doesn't want to work so here is a picture:
b: Pushed from 1 -> 2: 500
c: Pulled: 100
d: Pulled: 200
e: Pushed: 100
f: Pulled: 300
Pulled: 700
Pushed: 600
Thank you in advance and anymore question let me know!
I want to find the balance only, of those that have moved forwards and backwards, disregarding those who have been added or removed. For example in row f below that although a 100 balance was added to column 4, I only want the balance of the 300 that moved from column 3 to column 2.
Current Week:
ID | 1 | 2 | 3 | 4 |
---|---|---|---|---|
a | 100 | 0 | 0 | 0 |
b | 0 | 500 | 0 | 0 |
c | 300 | 100 | 0 | 0 |
d | 1200 | 200 | 100 | 0 |
e | 0 | 0 | 0 | 100 |
f | 200 | 300 | 0 | 100 |
Last Week:
ID | 1 | 2 | 3 | 4 |
---|---|---|---|---|
a | 0 | 0 | 0 | 100 |
b | 500 | 0 | 0 | 0 |
c | 300 | 0 | 100 | 0 |
d | 1200 | 0 | 100 | 200 |
e | 100 | 0 | 0 | 0 |
f | 200 | 0 | 300 | 0 |
For some reason the second table just doesn't want to work so here is a picture:
List of Movements are below:
a: Pulled forward from 4 -> 1: 100b: Pushed from 1 -> 2: 500
c: Pulled: 100
d: Pulled: 200
e: Pushed: 100
f: Pulled: 300
Output
This is what I would expect the output to look like in the end:Pulled: 700
Pushed: 600
Thank you in advance and anymore question let me know!