Find cell with lowest last number

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
Hard to explain what I'm trying to do in the title.

There is a link to the screenshot below, I'm having problems trying to add the image to this post

In column F: i want to find the cell with the lowest last number.
In this case it is F1791 as highlighted.
The last number could be anything from 1 digit to 5 digits long


s!AnlQC4WN-LeXoyc3VENJAv7JUulb
s!AnlQC4WN-LeXoyc3VENJAv7JUulb[COLOR=



Screenshot
https://1drv.ms/u/s!AnlQC4WN-LeXoyc3VENJAv7JUulb
s!AnlQC4WN-LeXoyc3VENJAv7JUulb
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,136
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What do you meant by "find the cell"... do you want the cell selected, colored, its row displayed in a MessageBox, something else?
 
Upvote 0

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
What do you meant by "find the cell"... do you want the cell selected, colored, its row displayed in a MessageBox, something else?

Its row displayed in a MessageBox would be great.

What would then occur if there are more than one number sharing the lowest?
 
Upvote 0

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
What do you want to happen?


If there's more than 1 row sharing the lowest last number it would be helpful to display them all in a message box.

Thanks for taking the time to help.
 
Upvote 0

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
OK I've finally worked out how to post a screenshot, this is where I'm hoping to get a message box to tell me the the row which contains the lowest last number. In this case its E1791 which ends in 5135, but these final numbers could be from 1 digit up to 5 digits

F
17871067, 1144, 1165, 1308, 1465, 1921, 2317, 2578, 2753, 3107, 3228, 3301, 4151, 4203, 5031, 5370, 6342, 6361,
1788622, 994, 1139, 1144, 1329, 1573, 1707, 1898, 2027, 2613, 2838, 3000, 5168, 5878, 6178, 6378, 6509, 6598,
1789681, 1027, 1144, 1165, 1279, 1366, 1485, 1882, 1898, 2350, 3145, 3639, 3656, 4051, 4098, 5031, 5551, 5743, 6000, 6221, 6460, 6598,
17903033, 3220, 3639, 4096, 4200, 4455, 4601, 5670, 6221, 6378, 6460, 6598,
17913773, 3807, 4098, 4424, 4750, 5135,
17923033, 3145, 3372, 4001, 4018, 4079, 4098, 5289, 5670, 6111, 6166, 6194,
17935670, 6756,
17946186, 6342, 6648,
1795259, 333, 401, 1289, 1292, 1641, 1836, 1920, 2268, 2411, 2562, 2587, 2702, 3134, 3407, 3864, 4500, 4882, 5025, 5437, 5506, 5717, 6316, 6626, 6713,
1796145, 420, 555, 656, 917, 1289, 1575, 1641, 2156, 2441, 2933, 4570, 4645, 4882, 5684, 6014, 6072, 6593,
1797350, 542, 849, 1099, 1112, 1162, 1836, 2026, 4216, 4562, 4876, 4929, 5941, 6014,

<tbody>
</tbody>
Combinations 3 of 47
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,136
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
If there's more than 1 row sharing the lowest last number it would be helpful to display them all in a message box.
Give this macro a try...
Code:
Sub LowestLastNumber()
  Dim R As Long, LastRow As Long, MinNum As Long, RowNum As Variant, LastNum As Variant, Data As Variant
  LastRow = Cells(Rows.Count, "F").End(xlUp).Row
  Data = Range("F1:F" & LastRow)
  MinNum = Evaluate("MIN(0+TRIM(LEFT(RIGHT(SUBSTITUTE(F2:F7,"","",REPT("" "",100)),200),100)))")
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "*," Then
      LastNum = Split(Data(R, 1), ",")
      LastNum = LastNum(UBound(LastNum) - 1)
      If LastNum = MinNum Then RowNum = RowNum & ", " & R
    End If
  Next
  RowNum = Mid(RowNum, 3)
  
  MsgBox "Minimum Number Row(s): " & RowNum
  
End Sub
 
Upvote 0

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
Give this macro a try...
Code:
Sub LowestLastNumber()
  Dim R As Long, LastRow As Long, MinNum As Long, RowNum As Variant, LastNum As Variant, Data As Variant
  LastRow = Cells(Rows.Count, "F").End(xlUp).Row
  Data = Range("F1:F" & LastRow)
  MinNum = Evaluate("MIN(0+TRIM(LEFT(RIGHT(SUBSTITUTE(F2:F7,"","",REPT("" "",100)),200),100)))")
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "*," Then
      LastNum = Split(Data(R, 1), ",")
      LastNum = LastNum(UBound(LastNum) - 1)
      If LastNum = MinNum Then RowNum = RowNum & ", " & R
    End If
  Next
  RowNum = Mid(RowNum, 3)
  
  MsgBox "Minimum Number Row(s): " & RowNum
  
End Sub

Message box appears but doesn't give a row number
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,136
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Message box appears but doesn't give a row number

Is your data in Column F like your example showed?

Do your cells end with a comma, again, as your example showed?
 
Last edited:
Upvote 0

Lewiskj

New Member
Joined
Feb 17, 2019
Messages
44
Office Version
  1. 365
Platform
  1. Windows
Is your data in Column F like your example showed?

yes exactly as the example shows

At the end of every row there is a space after the last comma, not sure if this will effect anything that you're code is looking for.
 
Upvote 0

Forum statistics

Threads
1,187,065
Messages
5,961,375
Members
438,539
Latest member
muimonk

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
Top