Avec VISUAL BASIC créer un tableau

Option Explicit

Private tabLieux()
Private TabPanne()
Private indLieux As Long
Private indPanne As Long


Sub main()

    indLieux = 0
    indPanne = 0

    initTableau
    
    comptePanneParLieu
    
    compteTypedePanneparPanne
    
End Sub

Public Sub initTableau()
Dim ws As Worksheet
Dim lig As Long

    Set ws = Worksheets(1)
    lig = 2
    ReDim tabLieux(indLieux)
    With ws
        While .Range("A" & lig).Value <> ""
            If doesExist(.Range("A" & lig).Value, tabLieux) = False Then
                ReDim Preserve tabLieux(indLieux)
                tabLieux(indLieux) = .Range("A" & lig).Value
                indLieux = indLieux + 1
            End If
        lig = lig + 1
        Wend
        
        lig = 2
        ReDim TabPanne(indPanne)
        While .Range("B" & lig).Value <> ""
            If doesExist(.Range("B" & lig).Value, TabPanne) = False Then
                ReDim Preserve TabPanne(indPanne)
                TabPanne(indPanne) = .Range("B" & lig).Value
                indPanne = indPanne + 1
            End If
        lig = lig + 1
        Wend
    End With
    
End Sub

Private Sub comptePanneParLieu()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lig1 As Long
Dim lig2 As Long
Dim i As Long
Dim cpt As Long

    Set ws1 = Worksheets(1)
    Set ws2 = Worksheets(2)
    lig2 = 2
    
    With ws1
        For i = LBound(tabLieux()) To UBound(tabLieux())
            cpt = 0
            lig1 = 2
            While .Range("A" & lig1).Value <> ""
                If .Range("A" & lig1).Value = tabLieux(i) Then
                    cpt = cpt + 1
                End If
            lig1 = lig1 + 1
            Wend
            ws2.Range("A" & lig2).Value = tabLieux(i)
            ws2.Range("B" & lig2).Value = cpt
            lig2 = lig2 + 1
        Next i
    End With
    
End Sub

Private Sub compteTypedePanneparPanne()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lig1 As Long
Dim lig2 As Long
Dim i As Long
Dim cpt As Long

    Set ws1 = Worksheets(1)
    Set ws2 = Worksheets(2)
    lig2 = 2
    
    With ws1
        For i = LBound(TabPanne()) To UBound(TabPanne())
            cpt = 0
            lig1 = 2
            While .Range("B" & lig1).Value <> ""
                If .Range("B" & lig1).Value = TabPanne(i) Then
                    cpt = cpt + 1
                End If
            lig1 = lig1 + 1
            Wend
            ws2.Range("D" & lig2).Value = TabPanne(i)
            ws2.Range("E" & lig2).Value = cpt
            lig2 = lig2 + 1
        Next i
    End With
End Sub

Private Function doesExist(ByVal str As Variant, ByRef tbl()) As Boolean
Dim i As Long

    For i = LBound(tbl()) To UBound(tbl())
        If tbl(i) = str Then
            doesExist = True
            Exit Function
        End If
    Next i
    
    doesExist = False
    
End Function