formula to compile the summary

[Naveenaggarwal]

Hi!,

In my Data Sheet, I am having below data.

[TABLE="width: 888"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Rejection Details
[/TD]
[TD]Jul-2018[/TD]
[TD]22[/TD]
[TD]34[/TD]
[TD]31[/TD]
[TD]22[/TD]
[TD]24[/TD]
[TD]22[/TD]
[TD]36[/TD]
[TD]36[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Model[/TD]
[TD]Part[/TD]
[TD]Production Qty.[/TD]
[TD]M.Cap Miss[/TD]
[TD]M.Dent Mark[/TD]
[TD]M.Fin[/TD]
[TD]M.Flow Mark[/TD]
[TD]M.Scratch[/TD]
[TD]M.Shrinkage[/TD]
[TD]M.Silver Marks[/TD]
[TD]M.Warpage[/TD]
[TD]M.Weld Line[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]17-07-18[/TD]
[TD]A[/TD]
[TD]Molding L[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]18-07-18[/TD]
[TD]B[/TD]
[TD]Upper (PMMA)[/TD]
[TD]600[/TD]
[TD]5[/TD]
[TD]26[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17-07-18[/TD]
[TD]A[/TD]
[TD]Rr Lic Grn Camera[/TD]
[TD]700[/TD]
[TD]17[/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]21[/TD]
[TD]7[/TD]
[TD]11[/TD]
[TD]17[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]


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

<colgroup><col style="mso-width-source:userset;mso-width-alt:3397;width:73pt" width="97"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 161, colspan: 2"]Top Defects
[/TD]

[TD="class: xl65"]Defects
[/TD]
[TD="class: xl65"]Qty[/TD]

[TD="class: xl66"]M.Silver Marks
[/TD]
[TD="class: xl67"]36[/TD]

[TD="class: xl66"]M.Warpage
[/TD]
[TD="class: xl67"]36[/TD]

[TD="class: xl66"]M.Dent Mark
[/TD]
[TD="class: xl67"]34[/TD]

[TD="class: xl66"]M.Fin
[/TD]
[TD="class: xl67"]31[/TD]

[TD="class: xl66"]M.Scratch
[/TD]
[TD="class: xl67"]24[/TD]

[TD="class: xl66"]M.Weld Line[/TD]
[TD="class: xl67"]23[/TD]

[TD="class: xl66"]M.Cap Miss
[/TD]
[TD="class: xl67"]22[/TD]

[TD="class: xl66"]M.Flow Mark
[/TD]
[TD="class: xl67"]22[/TD]

[TD="class: xl66"]M.Shrinkage
[/TD]
[TD="class: xl67"]22
[/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl67"]#N/A
[/TD]

</tbody>

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

<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>
[TD="class: xl66, width: 174, colspan: 3"]M.Silver Marks (Top Defect)
[/TD]

[TD="class: xl66"]Model
[/TD]
[TD="class: xl66"]Part Name
[/TD]
[TD="class: xl66"]Quantity[/TD]

[TD="class: xl66"]
[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]
[/TD]

[TD="class: xl66"]
[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl66"]
[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]
[/TD]

</tbody>

Please suggest the formula

Thanks

Naveen Aggarwal

 

[RasGhul]

	A	B	C
Model	Part	Quantity
A	Molding L
B	Upper (PMMA)
A	Rr Lic Grn Camera

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]M.Silver Marks[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]11[/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]