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

Leda Leong

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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Last edited:
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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)))))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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