counting based on 2 column data

[Leda Leong]

Dear all,

I would like to set a formula and show the below (count of Problem code) (top 3) with the below table information
May I know the formula of how to set to count the top 3 problem code and also the number count of problem code
thank you so much

[TABLE="width: 664"]
<tbody>[TR]
[TD] WO Shop [/TD]
[TD]Work Type [/TD]
[TD]Problem Code [/TD]
[TD]Count of Problem Code [/TD]
[/TR]
[TR]
[TD]PA-P3H-BMNT[/TD]
[TD]CM[/TD]
[TD]PAINTP[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PANELS[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]DOORP[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PA-P3H-SVRQ[/TD]
[TD]CM[/TD]
[TD]TIMELOX[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ACCTL[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SHOWER[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SRQST[/TD]
[TD]SAFE[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ACCTL[/TD]
[TD]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]



[TABLE="width: 199"]
<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <tbody>[TR]
[TD="class: xl65, width: 94, bgcolor: #E7E5E5"] WO Shop 工單組別[/TD]
[TD="class: xl65, width: 84, bgcolor: #E7E5E5"]Problem Code 問題[/TD]
[TD="class: xl65, width: 86, bgcolor: #E7E5E5"]Work Type 工種[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]DOORP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]SAFE[/TD]
[TD="class: xl66, bgcolor: transparent"]SRQST[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PANELS[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]SAFE[/TD]
[TD="class: xl66, bgcolor: transparent"]SRQST[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]ACCTL[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]SAFE[/TD]
[TD="class: xl66, bgcolor: transparent"]SRQST[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]ACCTL[/TD]
[TD="class: xl66, bgcolor: transparent"]SRQST[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]NOLIGHT[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]ACCOLD[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]ACCOLD[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]TOILET[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]ACCTL[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]ACCTL[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]TIMELOX[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]SAFE[/TD]
[TD="class: xl66, bgcolor: transparent"]SRQST[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]DOORP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]TIMELOX[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]DOORP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]SHOWER[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]MARBLE[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]SHOWER[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]TIMELOX[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]FURN[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]CUBICLE[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-SVRQ[/TD]
[TD="class: xl66, bgcolor: transparent"]TIMELOX[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PA-P3H-BMNT[/TD]
[TD="class: xl66, bgcolor: transparent"]PAINTP[/TD]
[TD="class: xl66, bgcolor: transparent"]CM[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Kind of tricky to do it with formulas. A few ideas. You could try using a Pivot Table. If your data is in A1:C33, try this: select A1:C33. Click the Insert tab, and click Pivot Table from the ribbon. Click OK on the dialog box, and it will create a new tab with the Pivot table Fields box on the right. Click on the WO Shop field and drag it to the Rows box. Click on Work Type, and drag it to the Rows box below WO Shop. Click on Problem Code and drag it below Work Type. Finally, drag Problem Code to the Values box. You should end up with this:

	A	B
Row Labels	Count of Problem Code ??
PA-P3H-BMNT
CM
DOORP
PAINTP
PANELS
CM Total
PA-P3H-BMNT Total
PA-P3H-SVRQ
CM
ACCOLD
ACCTL
CUBICLE
DOORP
FURN
MARBLE
NOLIGHT
SHOWER
TIMELOX
TOILET
CM Total
SRQST
ACCTL
SAFE
SRQST Total
PA-P3H-SVRQ Total
Grand Total

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet4​

It actually looks better in Excel, since it has indentation and bolding. All the categories are summarized. They are not sorted by frequency though, but alphabetically. There may be a way to use the filter, I'm not sure, I'm not a Pivot Table expert.


Next idea is to use formulas, but they are really complicated, and probably pretty slow, and don't look exactly like your example.

	A	B	C	D	E	F	G	H	I
PA-P3H-SVRQ	CM	TIMELOX
PA-P3H-SVRQ	CM	ACCTL
PA-P3H-SVRQ	CM	DOORP
PA-P3H-SVRQ	SRQST	SAFE
PA-P3H-SVRQ	SRQST	ACCTL
PA-P3H-SVRQ	SRQST
PA-P3H-BMNT	CM	PAINTP
PA-P3H-BMNT	CM	PANELS
PA-P3H-BMNT	CM	DOORP

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #E7E5E5"]WO Shop ????[/TD]
[TD="bgcolor: #E7E5E5"]Problem Code ??[/TD]
[TD="bgcolor: #E7E5E5"]Work Type ??[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E7E5E5"]WO Shop[/TD]
[TD="bgcolor: #E7E5E5"]Work Type[/TD]
[TD="bgcolor: #E7E5E5"]Problem Code[/TD]
[TD="bgcolor: #E7E5E5"]Count of Problem Code[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]DOORP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]SAFE[/TD]
[TD="bgcolor: #FAFAFA"]SRQST[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PANELS[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]SAFE[/TD]
[TD="bgcolor: #FAFAFA"]SRQST[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]ACCTL[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]SAFE[/TD]
[TD="bgcolor: #FAFAFA"]SRQST[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]ACCTL[/TD]
[TD="bgcolor: #FAFAFA"]SRQST[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]NOLIGHT[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]ACCOLD[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]ACCOLD[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]TOILET[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]ACCTL[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]ACCTL[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]TIMELOX[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]SAFE[/TD]
[TD="bgcolor: #FAFAFA"]SRQST[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]DOORP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]TIMELOX[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]DOORP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]SHOWER[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]MARBLE[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]SHOWER[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]TIMELOX[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]FURN[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]CUBICLE[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-SVRQ[/TD]
[TD="bgcolor: #FAFAFA"]TIMELOX[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA"]PA-P3H-BMNT[/TD]
[TD="bgcolor: #FAFAFA"]PAINTP[/TD]
[TD="bgcolor: #FAFAFA"]CM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]F2[/TH]
[TD="align: left"]{=IFERROR(IF(COUNTIF($F$1:$F1,F1)>=SUM(SIGN(FREQUENCY(IF($A$2:$A$33=F1,MATCH($A$2:$A$33&"|"&$C$2:$C$33,$A$2:$A$33&"|"&$C$2:$C$33,0)),ROW($A$2:$A$33)-ROW($A$2))))*3,INDEX($A$2:$A$33,SMALL(IF(COUNTIF($F$1:$F1,$A$2:$A$33)=0,ROW($A$2:$A$33)),1)),F1),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IF(F2="","",INDEX($C$2:$C$33,SMALL(IF($A$2:$A$33=F2,IF(MATCH(F2&"|"&$C$2:$C$33,$A$2:$A$33&"|"&$C$2:$C$33,0)=ROW($A$2:$A$33)-ROW($A$2)+1,ROW($A$2:$A$33)-ROW($A$2)+1)),INT((COUNTIF($F$2:$F2,F2)-1)/3)+1)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=IF(G2="","",IF(I2="","",INDEX($B$2:$B$33,SMALL(IF($A$2:$A$33=F2,IF($C$2:$C$33=G2,IF(COUNTIFS($A$2:$A$33,F2,$C$2:$C$33,G2,$B$2:$B$33,$B$2:$B$33)=I2,ROW($A$2:$A$33)-ROW($A$2)+1))),COUNTIFS($F$2:$F2,F2,$G$2:$G2,G2,$I$2:$I2,I2)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=IFERROR(IF(F2="","",LARGE(IF($A$2:$A$33=F2,IF($C$2:$C$33=G2,IF(MATCH($A$2:$A$33&"|"&$B$2:$B$33&"|"&$C$2:$C$33,$A$2:$A$33&"|"&$B$2:$B$33&"|"&$C$2:$C$33,0)=ROW($A$2:$A$33)-ROW($A$2)+1,COUNTIFS($A$2:$A$33,F2,$B$2:$B$33,$B$2:$B$33,$C$2:$C$33,G2)))),COUNTIFS($F$2:$F2,F2,$G$2:$G2,G2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put each formula in G2:I2 respectively, confirm with Control+Shift+Enter. Now copy F2:I2 and drag down as far as needed.


Final thought is that this is probably best done with a VBA macro. Let us know how this works for you.