VBA or Formula to return reference until volume is saturated then move on to returning the next referrence

[EmmaLS]

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?

[TABLE="width: 503"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]Imports tab
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Exports Tab
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Import Shipment
[/TD]
[TD]Reference
[/TD]
[TD]Volume
[/TD]
[TD] [/TD]
[TD]1
[/TD]
[TD]Export shipment
[/TD]
[TD]Volume
[/TD]
[TD]returned Reference
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD]ab123
[/TD]
[TD="align: right"]10000
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2500
[/TD]
[TD]ab123
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD]ac234
[/TD]
[TD="align: right"]15000
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]450
[/TD]
[TD]ab123
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD]ad345
[/TD]
[TD="align: right"]14000
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]300
[/TD]
[TD]ab123
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD]ae456
[/TD]
[TD="align: right"]6000
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]760
[/TD]
[TD]ab123
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]4400
[/TD]
[TD]ab123
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]1480
[/TD]
[TD]ab123
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]400
[/TD]
[TD]ab123 & ac234
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]500
[/TD]
[TD]ac234
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10000
[/TD]
[TD]ac234
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]4000
[/TD]
[TD]ac234
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]500
[/TD]
[TD]ac234 & ad345
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]450
[/TD]
[TD]ad345
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]6900
[/TD]
[TD]ad345
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]etc….
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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!

 

[StephenCrump]

Welcome to the Forum!

I think you're right about the cumulative columns making it easier. Try:

I2: =IF(INDEX(B$2:B$8,1+IFERROR(MATCH(H1,D$2:D$8,1),0))<>INDEX(B$2:B$8,1+IFERROR(MATCH(H2-0.001,D$2:D$8,1),0)),INDEX(B$2:B$8,1+IFERROR(MATCH(H1,D$2:D$8,1),0))& " to ","")&INDEX(B$2:B$8,1+IFERROR(MATCH(H2-0.001,D$2:D$8,1),0)) Copy down

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Import	Reference	Volume	Cumulative		Export	Volume	Cumulative	Reference
2	1	ab123	10,000	10,000		1	2,500	2,500	ab123
3	2	ac234	15,000	25,000		2	7,500	10,000	ab123
4	3	ad345	14,000	39,000		3	500	10,500	ac234
5	4	ae456	6,000	45,000		4	760	11,260	ac234
6	5	xxx	1,000	46,000		5	15,000	26,260	ac234 to ad345
7	6	yyy	5,000	51,000		6	12,740	39,000	ad345
8	6	zzz	20,000	71,000		7	400	39,400	ae456
9						8	500	39,900	ae456
10						9	10,000	49,900	ae456 to yyy
11						10	4,000	53,900	yyy to zzz
12						11	15,000	68,900	zzz
Sheet1