Count occurrences based on criteria in another column

Hi all,

Sorry if this type of question has been asked a lot. I've tried to do search for it but haven't been able to find a solution yet.

I'll try to explain this based on an example. Consider the following text:


12.1 1
2.3 4
0.9 2
1.1 3
7.4 1
There are essentially 16 possible values in Column B. For each value, I need to know how many times there's a corresponding value in Column A within a certain range, for example between 0 and 5, between 5 and 10, etc. In the end, I'll need a matrix with the Column B values running down one side, and the ranges across the top, and the number of occurrences filling the matrix.

<5 5<x<10 ....
1 0 1
2 1 ... etc.
3 ....
4
5


Hopefully this is clear. Please help! Thanks.

Try running this macro on the worksheet with the values:

Sub test2()
    Dim arrSize, i, k, j As Long, f As Boolean, v, v2
    
    arrSize = 1
    ReDim v(1 To arrSize)
    
    i = 1
    Do While Range("A" & i) <> ""
        f = False
        For k = 1 To UBound(v)
            If v(k) <> "" Then
                v2 = Split(v(k), Chr(9))
                If Format(v2(0), "@") = Format(Range("B" & i), "@") Then
                    f = True
                    Exit For
                End If
            Else
                f = True
                v(k) = Range("B" & i) & Chr(9) & 0 & Chr(9) & 0 & Chr(9) & 0
                Exit For
            End If
        Next
        If Not f Then
            arrSize = arrSize + 1
            ReDim Preserve v(1 To arrSize)
            v(UBound(v)) = Range("B" & i) & Chr(9) & 0 & Chr(9) & 0 & Chr(9) & 0
        End If
        Select Case Range("A" & i).Value
            Case Is < 5: j = 1
            Case 5 To 10: j = 2
            Case Is > 10: j = 3
        End Select
        v2 = Split(v(k), Chr(9))
        v(k) = v2(0) & Chr(9) & CLng(v2(1)) + IIf(j = 1, 1, 0) & Chr(9) & CLng(v2(2)) + IIf(j = 2, 1, 0) & Chr(9) & CLng(v2(3)) + IIf(j = 3, 1, 0)

        i = i + 1
    Loop
    
    With Sheets.Add
        .Range("B1:D1").Value = Array("< 5", "5 <= x <= 10", "> 10")
        For k = 1 To UBound(v)
            v2 = Split(v(k), Chr(9))
            .Range("A" & k + 1 & ":D" & k + 1).Value = Array(v2(0), v2(1), v2(2), v2(3))
        Next
        With .Sort
            .SortFields.Add Key:=Range("A2:" & Range("A" & rows.Count).End(xlUp).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Header = xlYes
            .SetRange Range("A1", Cells(Range("A" & rows.Count).End(xlUp).row, 4))
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
            .SortFields.Clear
        End With
    End With
    
End Sub