Find cell with lowest last number

[Lewiskj]

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

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

 

[Rick Rothstein]

What do you meant by "find the cell"... do you want the cell selected, colored, its row displayed in a MessageBox, something else?

 

[Lewiskj]

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?

 

[Rick Rothstein]

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

What do you want to happen?

 

[Lewiskj]

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.

 

[Lewiskj]

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
1067, 1144, 1165, 1308, 1465, 1921, 2317, 2578, 2753, 3107, 3228, 3301, 4151, 4203, 5031, 5370, 6342, 6361,
622, 994, 1139, 1144, 1329, 1573, 1707, 1898, 2027, 2613, 2838, 3000, 5168, 5878, 6178, 6378, 6509, 6598,
681, 1027, 1144, 1165, 1279, 1366, 1485, 1882, 1898, 2350, 3145, 3639, 3656, 4051, 4098, 5031, 5551, 5743, 6000, 6221, 6460, 6598,
3033, 3220, 3639, 4096, 4200, 4455, 4601, 5670, 6221, 6378, 6460, 6598,
3033, 3145, 3372, 4001, 4018, 4079, 4098, 5289, 5670, 6111, 6166, 6194,
5670, 6756,
6186, 6342, 6648,
259, 333, 401, 1289, 1292, 1641, 1836, 1920, 2268, 2411, 2562, 2587, 2702, 3134, 3407, 3864, 4500, 4882, 5025, 5437, 5506, 5717, 6316, 6626, 6713,
145, 420, 555, 656, 917, 1289, 1575, 1641, 2156, 2441, 2933, 4570, 4645, 4882, 5684, 6014, 6072, 6593,
350, 542, 849, 1099, 1112, 1162, 1836, 2026, 4216, 4562, 4876, 4929, 5941, 6014,

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

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

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

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

[TD="align: center"]1791[/TD]
[TD="bgcolor: #FFFFFF"]3773, 3807, 4098, 4424, 4750, 5135,[/TD]

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

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

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

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

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

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

</tbody>

Combinations 3 of 47​

 

[Rick Rothstein]

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...

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

 

[Lewiskj]

Give this macro a try...

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

 

[Rick Rothstein]

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?

 

[Lewiskj]

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.