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

E

exceldaddy

New Member
Joined
Jul 9, 2015
Messages
6
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Xbox[/TD]
[TD]8.123.12[/TD]
[/TR]
[TR]
[TD]Playstation[/TD]
[TD]7.213.55[/TD]
[/TR]
[TR]
[TD]Xbox[/TD]
[TD]8.999.121[/TD]
[/TR]
[TR]
[TD]Nintendo[/TD]
[TD]9.232.121[/TD]
[/TR]
[TR]
[TD]Playstation[/TD]
[TD]6.232.231[/TD]
[/TR]
</tbody>[/TABLE]

(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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Xbox[/TD]
[TD]8.999.121[/TD]
[/TR]
[TR]
[TD]Playstation[/TD]
[TD]6..232.231[/TD]
[/TR]
[TR]
[TD]Nintendo[/TD]
[TD]9.232.121[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for the time and help.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Excel 2012
ABCDEFG
Xbox8.123.12Xbox8.999.121
Playstation7.213.55Playstation6.232.231
Xbox8.999.121Nintendo9.232.121
Nintendo9.232.121
Playstation6.232.231

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]213[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]999[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]232[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]232[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​
helper column

Excel 2012
D

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/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"]D1[/TH]
[TD="align: left"]=VALUE(MID(B1, FIND(".",B1)+1, FIND(".", B1, FIND(".", B1)+1)-FIND(".",B1)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



copy down

Excel 2012
G
8.999.121

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

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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"]G1[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[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][/tr]
[tr][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td]Xbox[/td][td]8.123.12[/td][td][/td][td][/td][td]Xbox[/td][td]8.999.121[/td][/tr]

[tr][td]
3​
[/td][td]Playstation[/td][td]7.213.55[/td][td][/td][td][/td][td]Playstation[/td][td]6..232.231[/td][/tr]

[tr][td]
4​
[/td][td]Xbox[/td][td]8.999.121[/td][td][/td][td][/td][td]Nintendo[/td][td]9.232.121[/td][/tr]

[tr][td]
5​
[/td][td]Nintendo[/td][td]9.232.121[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]Playstation[/td][td]6.232.231[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=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:

Rich (BB code): 

=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
 
Upvote 0
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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Xbox
[/TD]
[TD]5.4.2221.2244[/TD]
[/TR]
[TR]
[TD]Xbox[/TD]
[TD]3.4.2334.5555[/TD]
[/TR]
[TR]
[TD]PlayStation[/TD]
[TD]3.4.3343.3423[/TD]
[/TR]
</tbody>[/TABLE]

Again, thank you for the help.
 
Upvote 0
exceldaddy said:
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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Xbox
[/TD]
[TD]5.4.2221.2244[/TD]
[/TR]
[TR]
[TD]Xbox[/TD]
[TD]3.4.2334.5555[/TD]
[/TR]
[TR]
[TD]PlayStation[/TD]
[TD]3.4.3343.3423[/TD]
[/TR]
</tbody>[/TABLE]

Again, thank you for the help.
Click to expand...

I assume you did not try post #3...
 
Upvote 0
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.

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl75, width: 192"][/TD]
[TD="class: xl75, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
exceldaddy said:
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.

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl75, width: 192"][/TD]
[TD="class: xl75, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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"...
 
Upvote 0
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:

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Xbox[/TD]
[TD]8.123.12[/TD]
[/TR]
[TR]
[TD]Playstation[/TD]
[TD]7.2.13.55[/TD]
[/TR]
[TR]
[TD]Xbox

Xbox[/TD]
[TD]8.99.9.121

0[/TD]
[/TR]
[TR]
[TD]Nintendo[/TD]
[TD]9.2332.121[/TD]
[/TR]
[TR]
[TD]Playstation

Nintendo[/TD]
[TD]6.232.231

0


[/TD]
[/TR]
</tbody>[/TABLE]



(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:

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Xbox[/TD]
[TD]8.99.9.121[/TD]
[/TR]
[TR]
[TD]Playstation[/TD]
[TD]7.2.13.55[/TD]
[/TR]
[TR]
[TD]Nintendo[/TD]
[TD]9.2332.121[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
exceldaddy said:
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.
Click to expand...

You have already a solution...

[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][/tr]
[tr][td]
2​
[/td][td]Xbox[/td][td]8.123.12[/td][td][/td][td][/td][td]Xbox[/td][td]8.99.9.121[/td][/tr]

[tr][td]
3​
[/td][td]Playstation[/td][td]7.2.13.55[/td][td][/td][td][/td][td]Playstation[/td][td]6.232.231[/td][/tr]

[tr][td]
4​
[/td][td]Xbox[/td][td]8.99.9.121[/td][td][/td][td][/td][td]Nintendo[/td][td]9.2332.121[/td][/tr]

[tr][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]Xbox[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]Nintendo[/td][td]9.2332.121[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]Playstation[/td][td]6.232.231[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]Nintendo[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Being able to pull out highest number when divided by dots and using multiple criteria with some numbers being 0
Replies
1
Views
578
exceldaddy
E
T
  • Question Question
Multiple Criteria INDEX and MATCH CSE Array Formula
Replies
1
Views
2K
Marcelo Branco
M

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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