Being able to pull out highest number when divided by dots and using multiple criteria

[exceldaddy]

Hi everyone,

I was wondering if it is possible for excel to pull out the highest number when the digits are divided by dots. Please note the amount of numbers in between the dots are not constant (although they are always between 1-4 numbers). For example I have a table that looks like:

Xbox	8.123.12
Playstation	7.213.55
Xbox	8.999.121
Nintendo	9.232.121
Playstation	6.232.231

<tbody>
</tbody>

(although it has over thousands of rows).

I am looking for my output to pull out the highest number while searching for a specific criteria. I expect my output to look something like:

Xbox	8.999.121
Playstation	6..232.231
Nintendo	9.232.121

<tbody>
</tbody>

Thank you in advance for the time and help.

 

[AlanY]

Excel 2012

	A	B	C	D	E	F	G
1	Xbox	8.123.12		123		Xbox	8.999.121
2	Playstation	7.213.55		213		Playstation	6.232.231
3	Xbox	8.999.121		999		Nintendo	9.232.121
4	Nintendo	9.232.121		232
5	Playstation	6.232.231		232

<tbody>
</tbody>

Sheet2​

helper column

Excel 2012

	D
1	123

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D1 =VALUE(MID(B1, FIND(".",B1)+1, FIND(".", B1, FIND(".", B1)+1)-FIND(".",B1)-1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

copy down

Excel 2012

	G
1	8.999.121

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula G1 {=INDEX($B$1:$B$2500,MATCH(F1&MAX(IF($A$1:$A$2500=F1,$D$1:$D$2500)),$A$1:$A$2500&$D$1:$D$2500,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​
2​	Xbox	8.123.12			Xbox	8.999.121
3​	Playstation	7.213.55			Playstation	6..232.231
4​	Xbox	8.999.121			Nintendo	9.232.121
5​	Nintendo	9.232.121
6​	Playstation	6.232.231
7​

F2, control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$6,MATCH(MAX(IF($A$2:$A$6=E2,SUBSTITUTE($B$2:$B$6,".","")+0)),
    IF($A$2:$A$6=E2,SUBSTITUTE($B$2:$B$6,".","")+0),0))

Since you have too many records to consider, you could add the V() function to you workbook and invoke instead:

=INDEX($B$2:$B$6,MATCH(MAX(V(IF($A$2:$A$6=E2,SUBSTITUTE($B$2:$B$6,".","")+0))),
    V(),0))

The VBA code for V() is:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[exceldaddy]

Thank you so much! I was actually confident it could not be done.

I was wondering, how would it be different if the numbers provided had 3 dots instead of one? In this case, we can assume that the second number between the dots is always the same.

Xbox	5.4.2221.2244
Xbox	3.4.2334.5555
PlayStation	3.4.3343.3423

<tbody>
</tbody>

Again, thank you for the help.

 

[Aladin Akyurek]

Thank you so much! I was actually confident it could not be done.

I was wondering, how would it be different if the numbers provided had 3 dots instead of one? In this case, we can assume that the second number between the dots is always the same.

Xbox	5.4.2221.2244
Xbox	3.4.2334.5555
PlayStation	3.4.3343.3423

<tbody>
</tbody>

Again, thank you for the help.

I assume you did not try post #3...

 

[exceldaddy]

Hey Aladin,

Thank you so much for the help! You're right, I initially didn't try your suggestion, I thought I needed to use the VBA at the bottom and it is something I am unfamiliar with. I was wondering if you (or anyone else) would be able to help me if there were various dots (2 to 4) in between different cells. And the values would be ordered from left to right.... for example 3.9.200 > 3.7.4.399

Again, thank you for the help. And thank you in advance to anyone who answers my next question.

<tbody>
</tbody>

 

[Aladin Akyurek]

Hey Aladin,

Thank you so much for the help! You're right, I initially didn't try your suggestion, I thought I needed to use the VBA at the bottom and it is something I am unfamiliar with. I was wondering if you (or anyone else) would be able to help me if there were various dots (2 to 4) in between different cells. And the values would be ordered from left to right.... for example 3.9.200 > 3.7.4.399

Again, thank you for the help. And thank you in advance to anyone who answers my next question.

<tbody>
</tbody>

Did you run the suggestion (the first formula) for the question in post #4 you had?

And, I did not get what you are saying above "about various dots"...

 

[exceldaddy]

Yes, I did. Unfortunately it did not work. Various dots meaning that the number of dots in one number may be different than the amount of dots in another number within the same category.

I've rephrased my original question:

Hi everyone,

I was wondering if it is possible for excel to pull out the highest number when the digits are divided by dots. Please note the amount of numbers in between the dots are not constant (although they are always between 1-4 numbers). For example I have a table that looks like:

Xbox	8.123.12
Playstation	7.2.13.55
Xbox Xbox	8.99.9.121 0
Nintendo	9.2332.121
Playstation Nintendo	6.232.231 0

<tbody>
</tbody>

(although it has over hundreds of rows).

I am looking for my output to pull out the highest number while searching for a specific criteria. I expect my output to look something like:

Xbox	8.99.9.121
Playstation	7.2.13.55
Nintendo	9.2332.121

<tbody>
</tbody>

Thank you in advance for the time and help.

I also do not have experience with macros, so if that could be avoided, that would be great.

 

[Aladin Akyurek]

Yes, I did. Unfortunately it did not work. Various dots meaning that the number of dots in one number may be different than the amount of dots in another number within the same category.

I've rephrased my original question:

[...]

I also do not have experience with macros, so if that could be avoided, that would be great.

You have already a solution...

Row\Col	A​	B​	C​	D​	E​	F​
2​	Xbox	8.123.12			Xbox	8.99.9.121
3​	Playstation	7.2.13.55			Playstation	6.232.231
4​	Xbox	8.99.9.121			Nintendo	9.2332.121
5​
6​	Xbox	0​
7​	Nintendo	9.2332.121
8​	Playstation	6.232.231
9​
10​	Nintendo	0​

F2, control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$10,MATCH(MAX(IF($A$2:$A$10=E2,
    SUBSTITUTE($B$2:$B$10,".","")+0)),IF($A$2:$A$10=E2,
    SUBSTITUTE($B$2:$B$10,".","")+0),0))

Control+shift+enter means: Press down the control and the shift keys at the same time, while you hit the enter key.