counting based on 2 column data

L

Leda Leong

New Member
Joined
Sep 25, 2017
Messages
11
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]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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:

AB
Row LabelsCount 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.

ABCDEFGHI
PA-P3H-SVRQCMTIMELOX
PA-P3H-SVRQCMACCTL
PA-P3H-SVRQCMDOORP
PA-P3H-SVRQSRQSTSAFE
PA-P3H-SVRQSRQSTACCTL
PA-P3H-SVRQSRQST
PA-P3H-BMNTCMPAINTP
PA-P3H-BMNTCMPANELS
PA-P3H-BMNTCMDOORP

<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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Countifs and do not count zero
Replies
1
Views
670
Cross Terrier
C
L
  • Question Question
find the highest value in a column and match the top 3 highest value in another column
2
Replies
11
Views
1K
Marcelo Branco
M
G
  • Question Question
Comparing data in two columns against two data ranges
Replies
2
Views
401
Galveston
G
C
  • Question Question
Counting formula based on at least four conditions
Replies
2
Views
654
Churchy LaFemme
C
B
  • Question Question
Countif based on range in other column
Replies
2
Views
2K
Besh
B

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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