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?

	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!

 

[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