Find the value that appears the most

[yoan6]

Hi,

On a tab I have the information below.
Loc Model
E76 WS-C2960G
D71 PROLIANT DL360 G4
E76 PROLIANT DL360 G7
E76 PROLIANT DL360 G7
E76 PROLIANT DL360 G5
A80 PROLIANT DL360 G5

When I am looking for the value which is link to the value E76 I get WS-C2960G. But I would like to get the value PROLIANT DL360 G7 which is the value that appears the most.

Do you know how I can do it?

For your information:
I using the formula: =INDEX(Sheet2!L:L;(MATCH(Sheet1!F3;Sheet2!Y:Y;0)))
Sheet2!L:L => Model
Sheet1!F3 => E76
Sheet2!Y:Y => Loc

 

[Comfy]

This is quite ugly but I think it works:

Excel 2010
	A	B
1	Loc	Model
2	E76	WS-C2960G
3	D71	PROLIANT DL360 G4
4	E76	PROLIANT DL360 G7
5	E76	PROLIANT DL360 G7
6	E76	PROLIANT DL360 G5
7	A80	PROLIANT DL360 G5
8
9
10
11	E76	PROLIANT DL360 G7
Sheet1

Cell Formulas
Range	Formula
B11	{=INDEX($B$2:$B$7,MATCH(MAX(COUNTIFS($B$2:$B$7,IF($A$2:$A$7=A11,$B$2:$B$7,""),$A$2:$A$7,A11)),COUNTIFS($B$2:$B$7,IF($A$2:$A$7=A11,$B$2:$B$7,""),$A$2:$A$7,A11),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[yoan6]

It tooks to much ressources on my computer.

Is it a way to have something lighter?

 

[Comfy]

It tooks to much ressources on my computer.

Is it a way to have something lighter?

Quite likely, but beyond my ability I'm afraid.

 

[XOR LX]

Hi.

Using the same set-up as Comfy, either:

Non-array:

=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A7=A11)*COUNTIF(B2:B7,B2:B7))),B2:B7)

Alternative CSE formula:

=INDEX(B2:B7,MODE(IF(A2:A7=A11,MATCH(B2:B7,B2:B7,0))))

I'd imagine that the latter is the more efficient of the two, despite being an array formula, though I'm not sure of that: LOOKUP and FREQUENCY can be quite fast.

Regards

 

[Comfy]

Hi.

Using the same set-up as Comfy, either:

Non-array:

=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A7=A11)*COUNTIF(B2:B7,B2:B7))),B2:B7)

I think this will need an adjustment.

Excel 2010

	A	B
PROLIANT DL360 G5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Loc[/TD]
[TD="bgcolor: #FFFFFF"]Model[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]E76[/TD]
[TD="bgcolor: #FFFFFF"]WS-C2960G[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]D71[/TD]
[TD="bgcolor: #FFFFFF"]PROLIANT DL360 G4[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]E76[/TD]
[TD="bgcolor: #FFFFFF"]PROLIANT DL360 G5[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]E76[/TD]
[TD="bgcolor: #FFFFFF"]PROLIANT DL360 G7[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]E76[/TD]
[TD="bgcolor: #FFFFFF"]PROLIANT DL360 G7[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]A80[/TD]
[TD="bgcolor: #FFFFFF"]PROLIANT DL360 G5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]E76[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A7=A12)*COUNTIF(B2:B7,B2:B7))),B2:B7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010
	B
12	PROLIANT DL360 G7
Sheet2

Cell Formulas
Range	Formula
B12	=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A7=A12)*COUNTIFS(B2:B7,B2:B7,A2:A7,A12))),B2:B7)

 

[XOR LX]

@Comfy

Why the proposed adjustment, sorry?

Edit: of course, we are both right! Your proposed use of COUNTIFS makes much more sense, but in that case we simply don't need to take the product with the other array:

=LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS(B2:B7,B2:B7,A2:A7,A12))),B2:B7)

Regards

 

[Comfy]

@Comfy

Why the proposed adjustment, sorry?

Edit: of course, we are both right! Your proposed use of COUNTIFS makes much more sense, but in that case we simply don't need to take the product with the other array:

=LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS(B2:B7,B2:B7,A2:A7,A12))),B2:B7)

Regards

Ah, Awesome.

 

[XOR LX]

Ah, Awesome.

Thanks to you!

Actually, although this construction is of interest, and in general may be more efficient than the equivalent MATCH/MAX CSE set-up, here we have MODE at our disposal, so I imagine that the other CSE formula I proposed with that function is the most efficient in this case. Not sure though!

Cheers