Finding Last Purchase Price For Vendor/Material - Sorted Untabbed Pivot Table?

[aware073]

Hey all,
I have two sheets in my workbook. The first has a list of materials, the second has a list of 2015 "receipts" of those materials - i.e. everytime they received that material into inventory, with the pricing.
Sheet 1 -

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Material	Plant	Material Description	Storage Bin	Valuated UnRes Stock	Ave Price	Stock Value	Price Unit	Safety Stock	Max. Level	Base UOM	2015 Usage	2015 Receipts	Vendor	Info Record Price
2	230665	M242	RECEPTACLE FEMALE SVC RFG50E00A-3B00Z	02020	1.000	2,148.84	2,148.84	1	1.000	1.000	EA	0	0	#N/A
3	236708	M242	RECEPTACLE GR MNTD SVC RMG50E00B-3B00	02020	1.000	1,960.84	1,960.84	1	1.000	1.000	EA	0	0	#N/A
4	219521	M242	PACKING ROPE 1/4 BENTLEY-HARRIS TS 1000	020200	188.000	0.37	69.56	1	1.000	500.000	FT	0	0	#N/A
5	233688	M242	PACKING ROPE 3/8 BENTLEY-HARRIS TS 1000	020200	400.000	0.84	336.00	1	1.000	500.000	FT	-84	0	#N/A
6	230774	M242	PLUG W/RECEPTACLE ALCO P371X38	02030	2.000	20.77	41.54	1	2.000	6.000	EA	0	0	#N/A
7	232111	M242	SHEAVE GOOD 321A382-5MK1	02030	1.000	214.00	214.00	1	1.000	2.000	EA	0	0	#N/A
8	219523	M242	PACKING ROPE 1/2 BENTLEY-HARRIS TS 1000	020300	500.000	1.02	510.00	1	1.000	500.000	FT	0	0	#N/A
9	224290	M242	FUSE 15.5KV MAX CUTL 15CX-6C	020301	2.000	156.98	313.96	1	1.000	2.000	EA	0	0	#N/A
10	235879	M242	FUSE BUSS 15.5CAVH1E	020301	8.000	23,019.63	1,841.57	100	2.000	5.000	EA	0	0	#N/A
11	219532	M242	RELAY GROUND TRIP L/P 07-0013	020302	2.000	163.53	327.06	1	1.000	1.000	EA	-1	2	602660
12	229146	M242	FUSE 2E 2.75-5.5KV GE 9F60BDD002	020302	3.000	81.13	243.39	1	1.000	1.000	EA	0	0	#N/A
13	237754	M242	FUSE 1.5 AMP 15.5KV MCGR FA4H1	020302	0.000	159.60	0.00	1	5.000	10.000	EA	-7	0	#N/A
14	225132	M242	RECEPTACLE FEMALE 16/C SVC C55-003R	020303	1.000	932.40	932.40	1	1.000	3.000	EA	0	1	602660
ZOPS SETUP

Sheet 2 -

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Plnt	Material	Material Description	Vendor	Pstng Date	Quantity in UnE	EUn	Amount in LC	Per
2	M242	218691	CPLG SLIP PVC 2IN SCH80	602590	09/15/2015	20.000	EA	305.40	15.27
3	M242	218703	FITTING 200LB 2IN AQUAMINE 290800002	602590	11/05/2015	30.000	EA	216.30	7.21
4	M242	218703	FITTING 200LB 2IN AQUAMINE 290800002	602590	10/22/2015	10.000	EA	72.10	7.21
5	M242	218703	FITTING 200LB 2IN AQUAMINE 290800002	602590	09/15/2015	30.000	EA	216.30	7.21
6	M242	218703	FITTING 200LB 2IN AQUAMINE 290800002	602590	03/09/2015	8.000	EA	57.68	7.21
7	M242	218703	FITTING 200LB 2IN AQUAMINE 290800002	602590	03/03/2015	12.000	EA	86.52	7.21
8	M242	218718	VLV FOOT BALL AL 2IN GREAT PLAINS 108	602590	10/22/2015	20.000	EA	470.00	23.5
9	M242	218718	VLV FOOT BALL AL 2IN GREAT PLAINS 108	602590	09/22/2015	4.000	EA	92.76	23.19
10	M242	218718	VLV FOOT BALL AL 2IN GREAT PLAINS 108	602590	09/15/2015	6.000	EA	139.14	23.19
11	M242	218718	VLV FOOT BALL AL 2IN GREAT PLAINS 108	602590	04/24/2015	20.000	EA	463.80	23.19
2015 RECEIPT

My first step was bringing in the most frequently used vendors for each material, which I did using a pivot table from sheet 2 that counted the appearance of every vendor which I sorted and then used a vlookup to bring it into sheet 1 (column N). I now have to find the lowest purchase price for that material from that most frequently used vendor.
Any suggestions? Another pivot table that will sort by pricing somehow?

 

[Rijnsent]

Hi aware073,

I think you should be able to solve it with one pivot table:
-make a pivot of your second sheet
-in the "Rows" box, drop Material and below that "Vendor"
-in the "Values" box, drop "Quantity in UnE" and "Per" (assuming the latter is your price per piece)
-On the Quantity in UnE, right click, Value Field Settings and select "Sum", for "Per" select "Min"
-Now you should have an overview of all materials with the number of items per vendor and the minimum price of that vendor, use the "Design" menu of the pivot table to "Show in Tabular form" and "Repeat all item labels"
-Sort the data by "Sum of Quantity in UnE", the top vendor of each material should be at the top, so you could do an easy VLOOKUP of the vendor, its quantity and its minimum price.

Hope that helps,

Koen

 

[aware073]

That worked awesome thank you so so much.

 

[aware073]

Hey Koen, Can you see any way to manipulate this table to show me the most recent purchase price from that vendor according to the dates in Column E

 

[Rijnsent]

Hi aware073,
I fiddled around a bit, but that seems near impossible with just a pivot... It's pretty easy to find the last day of a sale by a certain vendor of a certain product: just drag in the Pstng Date and select "Max" (format as date, the dates will now show up like numbers, e.g. 42000 for 27-dec-14). Then there are 2 solutions:
-add with formulas next to your pivot with and AVERAGEIFS formula to pull in the price from your data table (it's basically a VLOOKUP with multiple criteria, an AVERAGEIFS will do just fine here)
-of if you want it in your pivot table: add 2 columns to your data: one to calculate the last sales date of a product-vendor combination, that would be an array formula like this one:
={MAX(IF($B$2:$B$14=B2;IF($D$2:$D$14=D2;$E$2:$E$14;FALSE)))} (in cell J2, press CTRL+SHIFT+ENTER when you enter the formula, see Array Formulas for more details on array formulas
-then in column K enter a formula like =AVERAGEIFS($I$2:$I$14;$B$2:$B$14;B2;$D$2:$D$14;D2;$E$2:$E$14;J2) for the value, then expand the source of the pivot table and add a "Max" of that last column to get the right number.

Good luck ,

Koen