Index and Match or vlookup?

ddub25

Well-known Member
Joined
Jan 11, 2007
Messages
586
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 1Table 2
ItemQtyItemQty
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​
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,402
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2007
Messages
586
Jason

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

Dan
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
You can also try the advanced filtering to extract non duplicate values. Then sum it with SUMIF.
 

ddub25

Well-known Member
Joined
Jan 11, 2007
Messages
586

ADVERTISEMENT

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 1Pivot Table
ItemSourceQtyTotal PriceItemSourceQtyTotal
M20 NutNuts & Bolts Ltd
10​
£ 5.00M20 NutNuts & Bolts Ltd
15​
£ 7.50
M20 ThreadNuts & Bolts Ltd
10​
£ 5.00M20 ThreadNuts & Bolts Ltd
15​
£ 7.50
18mm Ply SheetSheet Materials Ltd
4​
£ 80.0018mm Ply SheetSheet Materials Ltd
10​
£ 200.00
4 x 2 TimberTimber Ltd
15​
£ 15.004 x 2 TimberTimber Ltd
41​
£ 41.00
2 x 2 TimberTimber Ltd
10​
£ 5.002 x 2 TimberTimber Ltd
40​
£ 20.00
12mm Ply SheetSheet Materials Ltd
4​
£ 60.0012mm Ply SheetSheet Materials Ltd
10​
£ 150.00
3 x 2 TimberTimber Ltd
12​
£ 15.003 x 2 TimberTimber Ltd
12​
£ 15.00
M20 NutNuts & Bolts Ltd
5​
£ 2.50
M20 ThreadNuts & Bolts Ltd
5​
£ 2.50
18mm Ply SheetSheet Materials Ltd
2​
£ 40.00
4 x 2 TimberTimber Ltd
6​
£ 6.00
18mm Ply SheetSheet Materials Ltd
4​
£ 80.00
4 x 2 TimberTimber Ltd
20​
£ 20.00
12mm Ply SheetSheet Materials Ltd
6​
£ 90.00
2 x 2 TimberTimber Ltd
30​
£ 15.00
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,402
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1Table 1
2ItemSourceQtyTotal PriceIndexRow LabelsMin of IndexSum of QtySum of Total Price
3M20 NutNuts & Bolts Ltd10£5.001M20 Nut115£7.50
4M20 ThreadNuts & Bolts Ltd10£5.002Nuts & Bolts Ltd115£7.50
518mm Ply SheetSheet Materials Ltd4£80.003M20 Thread215£7.50
64 x 2 TimberTimber Ltd15£15.004Nuts & Bolts Ltd215£7.50
72 x 2 TimberTimber Ltd10£5.00518mm Ply Sheet310£200.00
812mm Ply SheetSheet Materials Ltd4£60.006Sheet Materials Ltd310£200.00
93 x 2 TimberTimber Ltd12£15.0074 x 2 Timber441£41.00
10M20 NutNuts & Bolts Ltd5£2.508Timber Ltd441£41.00
11M20 ThreadNuts & Bolts Ltd5£2.5092 x 2 Timber540£20.00
1218mm Ply SheetSheet Materials Ltd2£40.0010Timber Ltd540£20.00
134 x 2 TimberTimber Ltd6£6.001112mm Ply Sheet610£150.00
1418mm Ply SheetSheet Materials Ltd4£80.0012Sheet Materials Ltd610£150.00
154 x 2 TimberTimber Ltd20£20.00133 x 2 Timber712£15.00
1612mm Ply SheetSheet Materials Ltd6£90.0014Timber Ltd712£15.00
172 x 2 TimberTimber Ltd30£15.0015Grand Total1143£441.00
Sheet1
Cell Formulas
RangeFormula
E3:E17E3=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,241
Office Version
  1. 365
Platform
  1. Windows
@ddub25
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,971
Messages
5,621,890
Members
415,864
Latest member
cybid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top