find the highest value in a column and match the top 3 highest value in another column

[Leda Leong]

Dear all,

I have try the below calculation method to count the highest value but it failed. it can only count the top 1 instead of top 3, suppose the top one in column E should be FIX and to find the Top 3 highest value which match FIX in column F should be SINK, SHOWER, FURN, but I failed, can another one help?
=LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS($E$2:$E$5311,'RAW DATA (1)'!O1,F2:F5311,F2:F5311,F2:F5311,"<>0"))),F2:F5311)


[TABLE="width: 443"]
<colgroup><col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;" span="5"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <tbody>[TR]
[TD="class: xl74, width: 96, bgcolor: #E7E5E5"]DATE[/TD]
[TD="class: xl74, width: 96, bgcolor: #E7E5E5"]J-Day[/TD]
[TD="class: xl74, width: 96, bgcolor: #E7E5E5"]MONTH[/TD]
[TD="class: xl74, width: 96, bgcolor: #E7E5E5"]Status 狀態[/TD]
[TD="class: xl74, width: 96, bgcolor: #E7E5E5"]Remedy[/TD]
[TD="class: xl78, width: 111, bgcolor: #E7E5E5"]Problem[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]POWERFAIL[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]DONE[/TD]
[TD="class: xl77, bgcolor: transparent"]GUEST[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]MOTOR-R[/TD]
[TD="class: xl77, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]HINGE[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]TIMELOX[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]SHOWER[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]HINGE[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]BATTERY[/TD]
[TD="class: xl77, bgcolor: transparent"]TIMELOX[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl77, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl77, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]MOVE[/TD]
[TD="class: xl77, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl77, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]DAMPER-F[/TD]
[TD="class: xl77, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]BLOW-F[/TD]
[TD="class: xl77, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]BLOW-F[/TD]
[TD="class: xl77, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]CARPET[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]REPLACE[/TD]
[TD="class: xl77, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]B-LED[/TD]
[TD="class: xl77, bgcolor: transparent"]NOLIGHT[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]BLOW-F[/TD]
[TD="class: xl77, bgcolor: transparent"]ACHOT[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]VAVBOX-R[/TD]
[TD="class: xl77, bgcolor: transparent"]ACHOT[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]SHOWER[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]SHOWER[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]B-LED[/TD]
[TD="class: xl77, bgcolor: transparent"]NOLIGHT[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]PRODUCTION[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]1-Sep[/TD]
[TD="class: xl77, bgcolor: transparent"]245[/TD]
[TD="class: xl77, bgcolor: transparent"]9[/TD]
[TD="class: xl77, bgcolor: transparent"]COMP[/TD]
[TD="class: xl77, bgcolor: transparent"]FIX[/TD]
[TD="class: xl77, bgcolor: transparent"]CARPET[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel

Not clear what you are looking for.
Questions:
Do you want to list the 3 most frequent values in column F whose value in column E = FIX?
RAW DATA (1)'!O1 = FIX?
Considering the data sample above, the results would be?
SHOWER
DHARDWARE
HINGE

M.

 

[Leda Leong]

Welcome to Mr Excel

Not clear what you are looking for.
Questions:
Do you want to list the 3 most frequent values in column F whose value in column E = FIX?
RAW DATA (1)'!O1 = FIX?
Considering the data sample above, the results would be?
SHOWER
DHARDWARE
HINGE

M.

I would like to ask, if base on column E, the highest value is FIX, I would like to find out the top 3 highest value in column F, which results would be DHARDWARE, SHOWER AND CARPET, I can do it by Pivot table but don't know how to set up a formula

[TABLE="width: 230"]
<tbody>[TR]
[TD]Remedy[/TD]
[TD]FIX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Count of Remedy[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]DHARDWARE[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SHOWER[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]CARPET[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]HINGE[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SINK[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]FURN[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]POWERFAIL[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PRODUCTION[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TIMELOX[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

Filter >> Top 10

Change 10 to 3.

Sort Z >> A on Count of Remedy

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#D9E1F2"]Remedy[/td][td="bgcolor:#D9E1F2"]FIX[/td][/tr]
[tr][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td="bgcolor:#D9E1F2"]Row Labels[/td][td="bgcolor:#D9E1F2"]Count of Remedy[/td][/tr]
[tr][td]

4​

[/td][td]SHOWER[/td][td]

3​

[/td][/tr]
[tr][td]

5​

[/td][td]DHARDWARE[/td][td]

3​

[/td][/tr]
[tr][td]

6​

[/td][td]SINK[/td][td]

2​

[/td][/tr]
[tr][td]

7​

[/td][td]CARPET[/td][td]

2​

[/td][/tr]
[tr][td]

8​

[/td][td]HINGE[/td][td]

2​

[/td][/tr]
[tr][td]

9​

[/td][td="bgcolor:#D9E1F2"]Grand Total[/td][td="bgcolor:#D9E1F2"]

12​

[/td][/tr]
[/table]

 

[Leda Leong]

thanks, but I would like a formula instead of using pivot table, would you please kindly teach me? thanks

 

[Marcelo Branco]

Maybe this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Remedy​

[/td][td]

List​

[/td][td]

Count​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

FIX​

[/td][td]

SHOWER​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

DHARDWARE​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

HINGE​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

SINK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

CARPET​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

POWERFAIL​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

TIMELOX​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td]

FURN​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td]

PRODUCTION​

[/td][td]

1​

[/td][/tr]
[/table]

Array formula in I2 copied down
=IFERROR(INDEX(F$2:F$5311,MODE(IF(E$2:E$5311=H$2,IF(ISNA(MATCH(F$2:F$5311,I$1:I1,0)),MATCH(F$2:F$5311,F$2:F$5311,0)+{0,0})))),"")
Ctrl+Shift+.Enter

Regular formula in J2 copied down
=IF(I2="","",COUNTIFS(E$2:E$5311,H$2,F$2:F$5311,I2))

Hope this helps

M.

 

[Leda Leong]

no, sorry, it failed, can I just type the below 2 column,
I would like to find the highest frequency in column E which answer is FIX.
and based on FIX, I would like to know the highest frequency for FIX in column F, and the answer should be SINK, ACHOT, AC-NOISY.

=LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS($E$2:$E$5311,'RAW DATA (1)'!O1,F2:F5311,F2:F5311,F2:F5311,"<>0"))),F2:F5311)

I Tried the above formula but it can only count SINK for me, but actually I would like to know the top 3 value for FIX in column F,

Can anyone help, million thanks


[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]Column E [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Column F[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #E7E5E5"]Remedy[/TD]
[TD="class: xl68, bgcolor: #E7E5E5"]Problem[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]POWERFAIL[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]DONE[/TD]
[TD="class: xl69, bgcolor: transparent"]GUEST[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]MOTOR-R[/TD]
[TD="class: xl69, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]HINGE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]TIMELOX[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SHOWER[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]HINGE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]BATTERY[/TD]
[TD="class: xl69, bgcolor: transparent"]TIMELOX[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl69, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl69, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]MOVE[/TD]
[TD="class: xl69, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl69, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]DAMPER-F[/TD]
[TD="class: xl69, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]BLOW-F[/TD]
[TD="class: xl69, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]BLOW-F[/TD]
[TD="class: xl69, bgcolor: transparent"]AC-NOISY[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]CARPET[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]REPLACE[/TD]
[TD="class: xl69, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]B-LED[/TD]
[TD="class: xl69, bgcolor: transparent"]NOLIGHT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]BLOW-F[/TD]
[TD="class: xl69, bgcolor: transparent"]ACHOT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]VAVBOX-R[/TD]
[TD="class: xl69, bgcolor: transparent"]ACHOT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SHOWER[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SHOWER[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]B-LED[/TD]
[TD="class: xl69, bgcolor: transparent"]NOLIGHT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]PRODUCTION[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]CARPET[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]DHARDWARE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]ACTVAL-R[/TD]
[TD="class: xl69, bgcolor: transparent"]ACHOT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]AIRDUCT-F[/TD]
[TD="class: xl69, bgcolor: transparent"]ACHOT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]ZR-OTHER[/TD]
[TD="class: xl69, bgcolor: transparent"]FITTING[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl69, bgcolor: transparent"]PAINTP[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]CARPET[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]DOORP[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl69, bgcolor: transparent"]FURN[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]OPEN[/TD]
[TD="class: xl69, bgcolor: transparent"]SAFE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]LATCH[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]TOILET[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]B-OTH[/TD]
[TD="class: xl69, bgcolor: transparent"]NOLIGHT[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]FIX[/TD]
[TD="class: xl69, bgcolor: transparent"]SINK[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]0[/TD]
[TD="class: xl69, bgcolor: transparent"]MARBLE[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]PAINT[/TD]
[TD="class: xl69, bgcolor: transparent"]PANELS[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

thanks, but I would like a formula instead of using pivot table, would you please kindly teach me? thanks

Something from my own *******...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#E7E5E5"] DATE[/td][td="bgcolor:#E7E5E5"] J-Day[/td][td="bgcolor:#E7E5E5"] MONTH[/td][td="bgcolor:#E7E5E5"] Status 狀態[/td][td="bgcolor:#E7E5E5"] Remedy[/td][td="bgcolor:#E7E5E5"] Problem[/td][td]

3​

[/td][/tr]
[tr][td]

2​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] POWERFAIL[/td][td="bgcolor:#FFE699"]FIX[/td][/tr]
[tr][td]

3​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] DONE[/td][td] GUEST[/td][td]Top Problems[/td][/tr]
[tr][td]

4​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] MOTOR-R[/td][td] AC-NOISY[/td][td]SHOWER[/td][/tr]
[tr][td]

5​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] HINGE[/td][td]DHARDWARE[/td][/tr]
[tr][td]

6​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] TIMELOX[/td][td]HINGE[/td][/tr]
[tr][td]

7​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] SHOWER[/td][td]SINK[/td][/tr]
[tr][td]

8​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] HINGE[/td][td]CARPET[/td][/tr]
[tr][td]

9​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] SINK[/td][td][/td][/tr]
[tr][td]

10​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] BATTERY[/td][td] TIMELOX[/td][td][/td][/tr]
[tr][td]

11​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] PAINT[/td][td] PAINTP[/td][td][/td][/tr]
[tr][td]

12​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] PAINT[/td][td] PAINTP[/td][td][/td][/tr]
[tr][td]

13​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] MOVE[/td][td] FURN[/td][td][/td][/tr]
[tr][td]

14​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] PAINT[/td][td] PAINTP[/td][td][/td][/tr]
[tr][td]

15​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] FURN[/td][td][/td][/tr]
[tr][td]

16​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] DHARDWARE[/td][td][/td][/tr]
[tr][td]

17​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] DHARDWARE[/td][td][/td][/tr]
[tr][td]

18​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] DHARDWARE[/td][td][/td][/tr]
[tr][td]

19​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] DAMPER-F[/td][td] AC-NOISY[/td][td][/td][/tr]
[tr][td]

20​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] BLOW-F[/td][td] AC-NOISY[/td][td][/td][/tr]
[tr][td]

21​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] BLOW-F[/td][td] AC-NOISY[/td][td][/td][/tr]
[tr][td]

22​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] CARPET[/td][td][/td][/tr]
[tr][td]

23​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] REPLACE[/td][td] FURN[/td][td][/td][/tr]
[tr][td]

24​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] B-LED[/td][td] NOLIGHT[/td][td][/td][/tr]
[tr][td]

25​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] BLOW-F[/td][td] ACHOT[/td][td][/td][/tr]
[tr][td]

26​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] VAVBOX-R[/td][td] ACHOT[/td][td][/td][/tr]
[tr][td]

27​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] SHOWER[/td][td][/td][/tr]
[tr][td]

28​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] SHOWER[/td][td][/td][/tr]
[tr][td]

29​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] B-LED[/td][td] NOLIGHT[/td][td][/td][/tr]
[tr][td]

30​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] SINK[/td][td][/td][/tr]
[tr][td]

31​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] PRODUCTION[/td][td][/td][/tr]
[tr][td]

32​

[/td][td] 1-Sep[/td][td] 245[/td][td] 9[/td][td] COMP[/td][td] FIX[/td][td] CARPET[/td][td][/td][/tr]
[/table]

1. Define Problem in the Name Manager as referring to:

=Sheet1!$F$2:$F$32

2. Define Remedy in the Name Manager as referring to:

=Sheet1!$E$2:$E$32

Adjust the sheet name as it suits.

3. Define Ivec in the Name Manager as referring to:

=ROW(Problem)-ROW(INDEX(Problem,1,1))+1

4. Define ProblemSet in the Name Manager as referring to:

=FREQUENCY(IF(1-(Problem=""),IF(Remedy=Sheet1!$I$2,MATCH(Problem,Problem,0))),Ivec)

where Sheet1!$I$2 houses a remedy of interest like FIX.

5. I1 houses Top N of interest where N = 3.

6. I2 houses, as said above, a remedy which is here set to FIX.

7. In I4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($I$4:I4)>SUM(IF(LARGE(ProblemSet,Ivec)>=LARGE(ProblemSet,$I$1),1)),"",INDEX(Problem,SMALL(IF(ProblemSet=LARGE(ProblemSet,ROWS($I$4:I4)),Ivec),SUM(IF(LARGE(ProblemSet,ROW($I$4:I4)-ROW($I$4)+1)=LARGE(ProblemSet,ROWS($I$4:I4)),1)))))

 

[Marcelo Branco]

no, sorry, it failed, can I just type the below 2 column,
I would like to find the highest frequency in column E which answer is FIX.
and based on FIX, I would like to know the highest frequency for FIX in column F, and the answer should be SINK, ACHOT, AC-NOISY.

I do not understand why ACHOT and AC-NOISY? In the data sample you showed there is no match between them and FIX.

Could you clarify?

The formulas in post #6 worked perfectly for me. The formula in column I must be confirmed with Ctrl+Shift+Enter, not just with Enter.

M.

 

[Leda Leong]

Maybe this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Remedy​

[/TD]
[TD]

List​

[/TD]
[TD]

Count​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

FIX​

[/TD]
[TD]

SHOWER​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD]

DHARDWARE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD]

HINGE​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD]

SINK​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD]

CARPET​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD]

POWERFAIL​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD]

TIMELOX​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD]

FURN​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD]

PRODUCTION​

[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in I2 copied down
=IFERROR(INDEX(F$2:F$5311,MODE(IF(E$2:E$5311=H$2,IF(ISNA(MATCH(F$2:F$5311,I$1:I1,0)),MATCH(F$2:F$5311,F$2:F$5311,0)+{0,0})))),"")
Ctrl+Shift+.Enter

Regular formula in J2 copied down
=IF(I2="","",COUNTIFS(E$2:E$5311,H$2,F$2:F$5311,I2))

Hope this helps

M.

thank you so much, it really works, really want burst to cry, thank you thank you