formula to compile the summary

Naveenaggarwal

New Member
Joined
Jul 17, 2018
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi!,

In my Data Sheet, I am having below data.

Rejection Details
Jul-2018223431222422363623
DateModelPartProduction Qty.M.Cap MissM.Dent MarkM.FinM.Flow MarkM.ScratchM.ShrinkageM.Silver MarksM.WarpageM.Weld Line
17-07-18AMolding L50005080913112
18-07-18BUpper (PMMA)600526190361280
17-07-18ARr Lic Grn Camera7001731214217111721

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


In other summary sheet, i have taken out the top 5 defects as below :

Top Defects
Defects
Qty
M.Silver Marks
36
M.Warpage
36
M.Dent Mark
34
M.Fin
31
M.Scratch
24
M.Weld Line23
M.Cap Miss
22
M.Flow Mark
22
M.Shrinkage
22
#N/A

<colgroup><col style="mso-width-source:userset;mso-width-alt:3397;width:73pt" width="97"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>


What i want is to create a list of main contributor from datasheet with respect to above top defects table. something like below :

M.Silver Marks (Top Defect)
Model
Part Name
Quantity






<colgroup><col style="mso-width-source:userset;mso-width-alt:1582;width:34pt" width="45"> <col style="mso-width-source:userset;mso-width-alt:2443;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2048;width:44pt" width="59"> </colgroup><tbody>
</tbody>
Please suggest the formula

Thanks

Naveen Aggarwal
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
ABC
1M.Silver Marks
2ModelPartQuantity
3AMolding L13
4BUpper (PMMA)12
5ARr Lic Grn Camera11

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Summary

Worksheet Formulas
CellFormula
A3=IFNA(INDEX(Data!$B$3:$M$5,ROWS($A$3:A3),MATCH(A$2,Data!$B$2:$M$2,0)),INDEX(Data!$B$3:$M$5,ROWS($A$3:A3),MATCH($A$1,Data!$B$2:$M$2,0)))
B3=IFNA(INDEX(Data!$B$3:$M$5,ROWS($A$3:B3),MATCH(B$2,Data!$B$2:$M$2,0)),INDEX(Data!$B$3:$M$5,ROWS($A$3:B3),MATCH($A$1,Data!$B$2:$M$2,0)))
C3=IFNA(INDEX(Data!$B$3:$M$5,ROWS($A$3:C3),MATCH(C$2,Data!$B$2:$M$2,0)),INDEX(Data!$B$3:$M$5,ROWS($A$3:C3),MATCH($A$1,Data!$B$2:$M$2,0)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top