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
1787	1067, 1144, 1165, 1308, 1465, 1921, 2317, 2578, 2753, 3107, 3228, 3301, 4151, 4203, 5031, 5370, 6342, 6361,
1788	622, 994, 1139, 1144, 1329, 1573, 1707, 1898, 2027, 2613, 2838, 3000, 5168, 5878, 6178, 6378, 6509, 6598,
1789	681, 1027, 1144, 1165, 1279, 1366, 1485, 1882, 1898, 2350, 3145, 3639, 3656, 4051, 4098, 5031, 5551, 5743, 6000, 6221, 6460, 6598,
1790	3033, 3220, 3639, 4096, 4200, 4455, 4601, 5670, 6221, 6378, 6460, 6598,
1791	3773, 3807, 4098, 4424, 4750, 5135,
1792	3033, 3145, 3372, 4001, 4018, 4079, 4098, 5289, 5670, 6111, 6166, 6194,
1793	5670, 6756,
1794	6186, 6342, 6648,
1795	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,
1796	145, 420, 555, 656, 917, 1289, 1575, 1641, 2156, 2441, 2933, 4570, 4645, 4882, 5684, 6014, 6072, 6593,
1797	350, 542, 849, 1099, 1112, 1162, 1836, 2026, 4216, 4562, 4876, 4929, 5941, 6014,

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