Index and Match or vlookup?

[ddub25]

Can anyone help with a formula for the below problem. I am not sure whether this can be done with a basic 'Index and Match or vlookup' formula or whether it needs to be done in visual basic. Anything I have tried doesn't seem to work.

Table 1 is a list of sub-assemblies and the materials and components required for each sub assembly. Table 2 simply lists all of the materials and components used across all the sub-assemblies and totals the quantity required.

Some pointers in the right direction would be much appreciated. Thanks

Dan

Table 1			Table 2
Item	Qty		Item	Qty
M20 Nut	10​		M20 Nut	15​
M20 Thread	10​		M20 Thread	15​
18mm Ply Sheet	4​		18mm Ply Sheet	6​
12mm Ply Sheet	4​		12mm Ply Sheet	10​
4 x 2 Timber	15​		4 x 2 Timber	41​
2 x 2 Timber	10​		2 x 2 Timber	40​
3 x 2 Timber	12​		3 x 2 Timber	12​
M20 Nut	5​
M20 Thread	5​
18mm Ply Sheet	2​
4 x 2 Timber	6​
18mm Ply Sheet	4​
4 x 2 Timber	20​
12mm Ply Sheet	6​
2 x 2 Timber	30​

 

[jasonb75]

Have you tried a pivot table?

Using Table 1 Items for row labels and Sum of Qty for Values would give you the same output as table 2.

 

[ddub25]

Jason

Thanks very much, that very useful. I've not used pivot tables before. Thanks again

Dan

 

[hnsd24_CN]

You can also try the advanced filtering to extract non duplicate values. Then sum it with SUMIF.

 

[ddub25]

I have been playing around with pivot tables a bit and realised that I would actually like to output my Pivot table with the results as below (I've added some extra columns). I can't seem to get the pivot table to display like this with the options within the pivot table dialogue.

I would also like the pivot to display the items in order as with the table I have suggested - 'M20 Nut' is the first item at the top of the table, 'M20 thread' is the 2nd item and so on as these items would have been found first at the top of table 1.

Can anyone help with getting the output to display like I have suggested? Thanks

Dan

Table 1					Pivot Table
Item	Source	Qty	Total Price		Item	Source	Qty	Total
M20 Nut	Nuts & Bolts Ltd	10​	£ 5.00		M20 Nut	Nuts & Bolts Ltd	15​	£ 7.50
M20 Thread	Nuts & Bolts Ltd	10​	£ 5.00		M20 Thread	Nuts & Bolts Ltd	15​	£ 7.50
18mm Ply Sheet	Sheet Materials Ltd	4​	£ 80.00		18mm Ply Sheet	Sheet Materials Ltd	10​	£ 200.00
4 x 2 Timber	Timber Ltd	15​	£ 15.00		4 x 2 Timber	Timber Ltd	41​	£ 41.00
2 x 2 Timber	Timber Ltd	10​	£ 5.00		2 x 2 Timber	Timber Ltd	40​	£ 20.00
12mm Ply Sheet	Sheet Materials Ltd	4​	£ 60.00		12mm Ply Sheet	Sheet Materials Ltd	10​	£ 150.00
3 x 2 Timber	Timber Ltd	12​	£ 15.00		3 x 2 Timber	Timber Ltd	12​	£ 15.00
M20 Nut	Nuts & Bolts Ltd	5​	£ 2.50
M20 Thread	Nuts & Bolts Ltd	5​	£ 2.50
18mm Ply Sheet	Sheet Materials Ltd	2​	£ 40.00
4 x 2 Timber	Timber Ltd	6​	£ 6.00
18mm Ply Sheet	Sheet Materials Ltd	4​	£ 80.00
4 x 2 Timber	Timber Ltd	20​	£ 20.00
12mm Ply Sheet	Sheet Materials Ltd	6​	£ 90.00
2 x 2 Timber	Timber Ltd	30​	£ 15.00

 

[jasonb75]

This is the closest you will be able to get with a pivot table, you can only have text labels in the first column. The 2 index columns could be hidden to tidy things up.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Table 1
2	Item	Source	Qty	Total Price	Index		Row Labels	Min of Index	Sum of Qty	Sum of Total Price
3	M20 Nut	Nuts & Bolts Ltd	10	£5.00	1		M20 Nut	1	15	£7.50
4	M20 Thread	Nuts & Bolts Ltd	10	£5.00	2		Nuts & Bolts Ltd	1	15	£7.50
5	18mm Ply Sheet	Sheet Materials Ltd	4	£80.00	3		M20 Thread	2	15	£7.50
6	4 x 2 Timber	Timber Ltd	15	£15.00	4		Nuts & Bolts Ltd	2	15	£7.50
7	2 x 2 Timber	Timber Ltd	10	£5.00	5		18mm Ply Sheet	3	10	£200.00
8	12mm Ply Sheet	Sheet Materials Ltd	4	£60.00	6		Sheet Materials Ltd	3	10	£200.00
9	3 x 2 Timber	Timber Ltd	12	£15.00	7		4 x 2 Timber	4	41	£41.00
10	M20 Nut	Nuts & Bolts Ltd	5	£2.50	8		Timber Ltd	4	41	£41.00
11	M20 Thread	Nuts & Bolts Ltd	5	£2.50	9		2 x 2 Timber	5	40	£20.00
12	18mm Ply Sheet	Sheet Materials Ltd	2	£40.00	10		Timber Ltd	5	40	£20.00
13	4 x 2 Timber	Timber Ltd	6	£6.00	11		12mm Ply Sheet	6	10	£150.00
14	18mm Ply Sheet	Sheet Materials Ltd	4	£80.00	12		Sheet Materials Ltd	6	10	£150.00
15	4 x 2 Timber	Timber Ltd	20	£20.00	13		3 x 2 Timber	7	12	£15.00
16	12mm Ply Sheet	Sheet Materials Ltd	6	£90.00	14		Timber Ltd	7	12	£15.00
17	2 x 2 Timber	Timber Ltd	30	£15.00	15		Grand Total	1	143	£441.00
Sheet1

Cell Formulas
Range	Formula
E3:E17	E3	=COUNT(D$3:D3)

If that is not close enough then I think that your exact layout should be possible with power query (I believe it should work but have not tried it yet). Other than that you would need some complex and inefficient formulas to do the task.

 

[Fluff]

@ddub25
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.