Master Detail with Pivots

[optikconnex]

I have a shipping table that shows what part to ship. I have another table that shows all the bins and on-hand quantities for that part. I want to make a master-detail report that shows the Part Number shipping info and shows all the details for the bin.

I cannot use a vlookup as that obviously only returns the one bin result. I know I can use some vba code but hoping there is an easier way.

Maybe using index,find,match, and offset?

Example:
Sheet one contains this info.


PartNum LineDesc NeedByDate OrderNum ShipToNum CustNum OrderQty
000tb0001 122772 3.00x10.00x1.38 STEERING CYL. 6/14/2011 72377 1 3 30


Sheet 2 contains this info:
PartNum WHE BinNum OnhandQty
000tb0001 SPL 3p131 1
000tb0001 TC1 0001 8
000tb0001 TC1 kn305 10

 

[Akashwani]

Hi,

Welcome to MrExcel.

Is this what you want?....

Excel Workbook
	A	B	C	D	E	F	G
1	PartNum	LineDesc	NeedByDate	OrderNum	ShipToNum	CustNum	OrderQty
2	000tb0001	122772 3.00x10.00x1.38 STEERING CYL.	6/14/11	72377	1	3	30
Sheet1

Excel Workbook
	A	B	C	D
1	PartNum	WHE	BinNum	OnhandQty
2	000tb0001	SPL	3p131	1
3	000tb0001	TC1	1	8
4	000tb0001	TC1	kn305	10
Sheet2

Excel Workbook
	A	B	C
1	PartNum	BinNum	3
2	000tb0001	3p131	*
3	*	1	*
4	*	kn305	*
Master

The formula in B2 needs entering with ctrl shift enter NOT enter.
You can then copy the formula down. You will need to change the ranges to suit your data.

I hope that works for you.

Ak