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)

DATE	J-Day	MONTH	Status 狀態	Remedy	Problem
1-Sep	245	9	COMP	FIX	POWERFAIL
1-Sep	245	9	COMP	DONE	GUEST
1-Sep	245	9	COMP	MOTOR-R	AC-NOISY
1-Sep	245	9	COMP	FIX	HINGE
1-Sep	245	9	COMP	FIX	TIMELOX
1-Sep	245	9	COMP	FIX	SHOWER
1-Sep	245	9	COMP	FIX	HINGE
1-Sep	245	9	COMP	FIX	SINK
1-Sep	245	9	COMP	BATTERY	TIMELOX
1-Sep	245	9	COMP	PAINT	PAINTP
1-Sep	245	9	COMP	PAINT	PAINTP
1-Sep	245	9	COMP	MOVE	FURN
1-Sep	245	9	COMP	PAINT	PAINTP
1-Sep	245	9	COMP	FIX	FURN
1-Sep	245	9	COMP	FIX	DHARDWARE
1-Sep	245	9	COMP	FIX	DHARDWARE
1-Sep	245	9	COMP	FIX	DHARDWARE
1-Sep	245	9	COMP	DAMPER-F	AC-NOISY
1-Sep	245	9	COMP	BLOW-F	AC-NOISY
1-Sep	245	9	COMP	BLOW-F	AC-NOISY
1-Sep	245	9	COMP	FIX	CARPET
1-Sep	245	9	COMP	REPLACE	FURN
1-Sep	245	9	COMP	B-LED	NOLIGHT
1-Sep	245	9	COMP	BLOW-F	ACHOT
1-Sep	245	9	COMP	VAVBOX-R	ACHOT
1-Sep	245	9	COMP	FIX	SHOWER
1-Sep	245	9	COMP	FIX	SHOWER
1-Sep	245	9	COMP	B-LED	NOLIGHT
1-Sep	245	9	COMP	FIX	SINK
1-Sep	245	9	COMP	FIX	PRODUCTION
1-Sep	245	9	COMP	FIX	CARPET

<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>
</tbody>

 

[Aladin Akyurek]

You seem to have skipped #8 . That gives you exactly the results the pivot table returns.

Here is the workbook which implements the se up: https://www.dropbox.com/s/4imytav8zfzwk6w/leda leong remedy top 3.xlsx?dl=0

 

[Marcelo Branco]

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

You are very welcome. Glad to help

M.