Excel Smart Price Buckets "Pivot" or VB Assistance

[Adler212]

Hello eveyone,

Im looking for asssitance on calculating a buket level and total buckets, based on a comparison to another sheet ( Red culms)

In order to emphesize here is an example;


Sheet A ( Spend)

[TABLE="width: 1200"]
<colgroup><col span="4"><col><col span="2"><col><col span="6"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]PO Number[/TD]
[TD]Vendor[/TD]
[TD]Vendor ID[/TD]
[TD]Item Desc[/TD]
[TD]Item[/TD]
[TD]Main Category[/TD]
[TD]Sec Category[/TD]
[TD]Currency[/TD]
[TD]Unit Price[/TD]
[TD]Quantity[/TD]
[TD]Total Value[/TD]
[TD]Site[/TD]
[TD]Year[/TD]
[TD]Bucket[/TD]
[TD]Total Buckets[/TD]
[/TR]
[TR]
[TD]01/01/2016[/TD]
[TD]1111111[/TD]
[TD]Vendor A[/TD]
[TD]10891[/TD]
[TD]Yellow Package [/TD]
[TD]1000015[/TD]
[TD]Package[/TD]
[TD]Flexible[/TD]
[TD]EUR[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]300[/TD]
[TD]Site A[/TD]
[TD]2016[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]02/02/2016[/TD]
[TD]1111112[/TD]
[TD]Vendor A[/TD]
[TD]10891[/TD]
[TD]Yellow Package [/TD]
[TD]1000015[/TD]
[TD]Package[/TD]
[TD]Flexible[/TD]
[TD]EUR[/TD]
[TD]10[/TD]
[TD]200[/TD]
[TD]2,000[/TD]
[TD]Site A[/TD]
[TD]2016[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]03/03/2016[/TD]
[TD]1111113[/TD]
[TD]Vendor B[/TD]
[TD]10892[/TD]
[TD]Yellow Package [/TD]
[TD]1000064[/TD]
[TD]Package[/TD]
[TD]Plastic[/TD]
[TD]EUR[/TD]
[TD]38.55[/TD]
[TD]500[/TD]
[TD]19,275[/TD]
[TD]Site A[/TD]
[TD]2016[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Sheet B ( Agreements):

[TABLE="width: 654"]
<colgroup><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Vendor[/TD]
[TD]Vendor Description[/TD]
[TD]Agreement[/TD]
[TD]Currency[/TD]
[TD]Quantity[/TD]
[TD]Unit Price[/TD]
[TD]Bucket[/TD]
[/TR]
[TR]
[TD]1000015[/TD]
[TD]10891[/TD]
[TD]Vendor A[/TD]
[TD]1005762[/TD]
[TD]EUR[/TD]
[TD]5[/TD]
[TD]41.3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1000015[/TD]
[TD]10891[/TD]
[TD]Vendor A[/TD]
[TD]1005762[/TD]
[TD]EUR[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1000015[/TD]
[TD]10891[/TD]
[TD]Vendor A[/TD]
[TD]1005762[/TD]
[TD]EUR[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1000015[/TD]
[TD]10891[/TD]
[TD]Vendor A[/TD]
[TD]1005762[/TD]
[TD]EUR[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1000015[/TD]
[TD]10891[/TD]
[TD]Vendor A[/TD]
[TD]1005762[/TD]
[TD]EUR[/TD]
[TD]40[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1000015[/TD]
[TD]10891[/TD]
[TD]Vendor A[/TD]
[TD]1005762[/TD]
[TD]EUR[/TD]
[TD]50[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1000064[/TD]
[TD]10892[/TD]
[TD]Vendor B[/TD]
[TD]4000085[/TD]
[TD]USD[/TD]
[TD]10[/TD]
[TD]55[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1000064[/TD]
[TD]10892[/TD]
[TD]Vendor B[/TD]
[TD]4000085[/TD]
[TD]USD[/TD]
[TD]100[/TD]
[TD]30.74[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1000064[/TD]
[TD]10892[/TD]
[TD]Vendor B[/TD]
[TD]4000085[/TD]
[TD]USD[/TD]
[TD]200[/TD]
[TD]24.48[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1000064[/TD]
[TD]10892[/TD]
[TD]Vendor B[/TD]
[TD]4000085[/TD]
[TD]USD[/TD]
[TD]300[/TD]
[TD]38.55[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Im looking for a way to calculate in the spend Sheet the buckets that appear in the Agreements Sheet. Somehow it's a Vlookup within a Vlookup but i cannot figure out how to properly design the formula so it will be based on the Item ID, and then calculate how many buckets does this crossed Item & Vendor include in the Agreement sheet, and on what bucket does it fall on ( per line).

I would appreciate any assistance on the matter,
Thanks in advance,
Asaf.