Hi
This is my first post so I hope I am doing everything correctly.
I have 2 tabs of data one for imports and one for exports. What I need to do is:
If cumulative value of exports is less than the imported volume of shipment 1, return the reference for shipment 1. Once the volume of shipment 1 is exceeded move on to returning the reference of shipment 2 (if some value of shipment 1 is covered but not all then return both the reference of shipment 1 and 2). Continue copying shipment 2 reference until shipment 2 volume is covered in the same way then move on to shipment 3 reference etc...
Below is a simplified version of the data and the column Returned reference is showing what I want to return in that column. Can you please advise the best way to do this?
<tbody>
</tbody>
The only way I have of achieving this currently is adding a cumulative volume column and using lots of nested IF statements which doesn't seem the best way. it seems to me a macro could solve this really easily but I am not experienced at building macros from scratch so would really appreciate some help or if you could suggest a formula that will not become overcomplicated and potentially exceeed the number of nested IFs allowed!)
Many Thanks in advance!
This is my first post so I hope I am doing everything correctly.
I have 2 tabs of data one for imports and one for exports. What I need to do is:
If cumulative value of exports is less than the imported volume of shipment 1, return the reference for shipment 1. Once the volume of shipment 1 is exceeded move on to returning the reference of shipment 2 (if some value of shipment 1 is covered but not all then return both the reference of shipment 1 and 2). Continue copying shipment 2 reference until shipment 2 volume is covered in the same way then move on to shipment 3 reference etc...
Below is a simplified version of the data and the column Returned reference is showing what I want to return in that column. Can you please advise the best way to do this?
Imports tab | Exports Tab | |||||||
A | B | C | A | B | C | |||
1 | Import Shipment | Reference | Volume | 1 | Export shipment | Volume | returned Reference | |
2 | 1 | ab123 | 10000 | 2 | 1 | 2500 | ab123 | |
3 | 2 | ac234 | 15000 | 3 | 2 | 450 | ab123 | |
4 | 3 | ad345 | 14000 | 4 | 3 | 300 | ab123 | |
5 | 4 | ae456 | 6000 | 5 | 4 | 760 | ab123 | |
6 | 6 | 5 | 4400 | ab123 | ||||
7 | 6 | 1480 | ab123 | |||||
8 | 7 | 400 | ab123 & ac234 | |||||
9 | 8 | 500 | ac234 | |||||
10 | 9 | 10000 | ac234 | |||||
11 | 10 | 4000 | ac234 | |||||
12 | 11 | 500 | ac234 & ad345 | |||||
13 | 12 | 450 | ad345 | |||||
14 | 13 | 6900 | ad345 | |||||
15 | etc…. |
<tbody>
</tbody>
The only way I have of achieving this currently is adding a cumulative volume column and using lots of nested IF statements which doesn't seem the best way. it seems to me a macro could solve this really easily but I am not experienced at building macros from scratch so would really appreciate some help or if you could suggest a formula that will not become overcomplicated and potentially exceeed the number of nested IFs allowed!)
Many Thanks in advance!