Assign a variable to cells to compare mutliple numbers

Question

I have a data set where I need to compare the first number in each transect against each other.

For example, in the below data set I need to compare cells D2, D7, D12 and D17 and assign a value based on which one is the smallest number, the next smallest and so on. This will be used to assign the transect numbers in column A.

My issue is that the number of sections (in this example 4) and the number of transects (also 4 in this example) will vary. So the cells I need to compare will change. I have written the code that calculates the number of transects, which is:

Dim tlength As Worksheet
Dim tb As Long *'tb=transect break*

Sub tlength_start_stop_coords()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("sheet1")
Set tlength = ThisWorkbook.Worksheets("transect lengths") *' assigns the sheet to a variable 
 for efficient coding*

tb = 0 *'counter to calculate the number of transects*
j = 2 *'counter for row*
Lastrow = Lastrow + 1 *'add a row to last row so that the last row includes a blank line.*
*'the following for loop is used to calculate the number of transects*

For i = 2 To Lastrow
   If tlength.Range("D" & i) = vbNullString Then
     If tlength.Range("D" & i - 1) <> vbNullString Then
       tb = tb + 1 *'updates the counter*
     End If
   End If
Next i
tbtotal = tb *'stores the total counter in variable tbtotal for later use*

I think I may need to use a loop. But I am stuck trying to figure out how to manage the unknown number of comparisons in changing cell locations.

The desired result is in the below screenshot of the expected outcome, with results in column A. To begin with, I only need to get the number for the first row of each transect. Once I have that, I can copy using xldown. Expected outcome:

Another possible data set and outcome expected might be:

enter image description here with an expected outcome of:

enter image description here

Answer 1

Worked for me using your second set of sample data:

Sub Tester()
    
    Dim tlength As Worksheet, i As Long, tNum As Long, v, vPrev, arr
    Dim col As New Collection, e, c As Range, rng As Range
    
    Set tlength = ThisWorkbook.Worksheets("transect lengths")
    
    'collect all the Section 1 Latitudes and row numbers
    For i = 2 To tlength.Cells(Rows.Count, "B").End(xlUp).Row
        If tlength.Cells(i, "B") = 1 Then
            col.Add Array(i, tlength.Cells(i, "D").Value) 'store start row and first Latitude
        End If
    Next i
    
    SortCollection col, 2 'sort collection by second element in each array
    
    tNum = 0
    'loop over the sorted collection and assign the order of the transects
    For Each e In col
        tNum = tNum + 1
        Set c = tlength.Cells(e(0), "B")
        'following assumes all transects have at least 2 sections...
        tlength.Range(c, c.End(xlDown)).Offset(0, -1).Value = tNum
    Next e
     
End Sub

'Sort a collection of 0-based arrays in ascending order by the n'th element of each array
'Adapted from https://stackoverflow.com/a/3588073/478884
Sub SortCollection(col As Collection, n As Long)
    Dim i As Long, j As Long, vTemp As Variant
    For i = 1 To col.Count - 1 'Two loops to bubble sort
        For j = i + 1 To col.Count
            If col(i)(n - 1) < col(j)(n - 1) Then  'change to > for ascending sort
                vTemp = col(j)                     'store the lesser item
                col.Remove j                       'remove the lesser item
                col.Add Item:=vTemp, before:=i     're-add the lesser item before the greater Item
            End If
        Next j
    Next i
End Sub