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

Leda Leong · Sep 25, 2017

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

Marcelo Branco · Sep 26, 2017

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 · Sep 26, 2017

Marcelo Branco said:
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

Remedy	FIX

Row Labels	Count of Remedy
Grand Total	16
DHARDWARE	3
SHOWER	3
CARPET	2
HINGE	2
SINK	2
FURN	1
POWERFAIL	1
PRODUCTION	1
TIMELOX	1

Aladin Akyurek · Sep 26, 2017

Filter >> Top 10

Change 10 to 3.

Sort Z >> A on Count of Remedy

Row\Col	A	B
1	Remedy	FIX
2
3	Row Labels	Count of Remedy
4	SHOWER	3
5	DHARDWARE	3
6	SINK	2
7	CARPET	2
8	HINGE	2
9	Grand Total	12

Leda Leong · Sep 26, 2017

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

Marcelo Branco · Sep 26, 2017

Maybe this

	H	I	J
1	Remedy	List	Count
2	FIX	SHOWER	3
3		DHARDWARE	3
4		HINGE	2
5		SINK	2
6		CARPET	2
7		POWERFAIL	1
8		TIMELOX	1
9		FURN	1
10		PRODUCTION	1

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 · Sep 26, 2017

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

Column E	Column F
Remedy	Problem
FIX	POWERFAIL
DONE	GUEST
MOTOR-R	AC-NOISY
FIX	HINGE
FIX	TIMELOX
FIX	SHOWER
FIX	HINGE
FIX	SINK
BATTERY	TIMELOX
PAINT	PAINTP
PAINT	PAINTP
MOVE	FURN
PAINT	PAINTP
FIX	FURN
FIX	DHARDWARE
FIX	DHARDWARE
FIX	DHARDWARE
DAMPER-F	AC-NOISY
BLOW-F	AC-NOISY
BLOW-F	AC-NOISY
FIX	CARPET
REPLACE	FURN
B-LED	NOLIGHT
BLOW-F	ACHOT
VAVBOX-R	ACHOT
FIX	SHOWER
FIX	SHOWER
B-LED	NOLIGHT
FIX	SINK
FIX	PRODUCTION
FIX	CARPET
FIX	DHARDWARE
FIX	SINK
ACTVAL-R	ACHOT
AIRDUCT-F	ACHOT
FIX	SINK
ZR-OTHER	FITTING
PAINT	PAINTP
FIX	CARPET
FIX	DOORP
PAINT	FURN
OPEN	SAFE
FIX	LATCH
FIX	TOILET
FIX	SINK
B-OTH	NOLIGHT
FIX	SINK
0	MARBLE
PAINT	PANELS

Aladin Akyurek · Sep 26, 2017

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

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

Row\Col	A	B	C	D	E	F	I
1	DATE	J-Day	MONTH	Status 狀態	Remedy	Problem	3
2	1-Sep	245	9	COMP	FIX	POWERFAIL	FIX
3	1-Sep	245	9	COMP	DONE	GUEST	Top Problems
4	1-Sep	245	9	COMP	MOTOR-R	AC-NOISY	SHOWER
5	1-Sep	245	9	COMP	FIX	HINGE	DHARDWARE
6	1-Sep	245	9	COMP	FIX	TIMELOX	HINGE
7	1-Sep	245	9	COMP	FIX	SHOWER	SINK
8	1-Sep	245	9	COMP	FIX	HINGE	CARPET
9	1-Sep	245	9	COMP	FIX	SINK
10	1-Sep	245	9	COMP	BATTERY	TIMELOX
11	1-Sep	245	9	COMP	PAINT	PAINTP
12	1-Sep	245	9	COMP	PAINT	PAINTP
13	1-Sep	245	9	COMP	MOVE	FURN
14	1-Sep	245	9	COMP	PAINT	PAINTP
15	1-Sep	245	9	COMP	FIX	FURN
16	1-Sep	245	9	COMP	FIX	DHARDWARE
17	1-Sep	245	9	COMP	FIX	DHARDWARE
18	1-Sep	245	9	COMP	FIX	DHARDWARE
19	1-Sep	245	9	COMP	DAMPER-F	AC-NOISY
20	1-Sep	245	9	COMP	BLOW-F	AC-NOISY
21	1-Sep	245	9	COMP	BLOW-F	AC-NOISY
22	1-Sep	245	9	COMP	FIX	CARPET
23	1-Sep	245	9	COMP	REPLACE	FURN
24	1-Sep	245	9	COMP	B-LED	NOLIGHT
25	1-Sep	245	9	COMP	BLOW-F	ACHOT
26	1-Sep	245	9	COMP	VAVBOX-R	ACHOT
27	1-Sep	245	9	COMP	FIX	SHOWER
28	1-Sep	245	9	COMP	FIX	SHOWER
29	1-Sep	245	9	COMP	B-LED	NOLIGHT
30	1-Sep	245	9	COMP	FIX	SINK
31	1-Sep	245	9	COMP	FIX	PRODUCTION
32	1-Sep	245	9	COMP	FIX	CARPET

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 · Sep 26, 2017

Leda Leong said:
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 · Sep 26, 2017

Marcelo Branco said:
Maybe this

H

I

J

1

Remedy

List

Count

2

FIX

SHOWER

3

3

DHARDWARE

3

4

HINGE

2

5

SINK

2

6

CARPET

2

7

POWERFAIL

1

8

TIMELOX

1

9

FURN

1

10

PRODUCTION

1

<tbody>
</tbody>

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

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

Leda Leong

New Member

Excel Facts

Marcelo Branco

MrExcel MVP

Leda Leong

New Member

Aladin Akyurek

MrExcel MVP

Leda Leong

New Member

Marcelo Branco

MrExcel MVP

Leda Leong

New Member

Aladin Akyurek

MrExcel MVP

Marcelo Branco

MrExcel MVP

Leda Leong

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock