Tukukkk

I've attempted to erite some VBA for this this question. The output is OK

but the code is not very elegant. I'm happy-ish with creating unique list of names and certificates, but the rest seems rather ugly. I'd love to learn how to make it more elegant and programmer-like (and less amateur-like-crap).

Sub PivotData()



    Dim rng As Range, cll As Range

    Dim arr As New Collection, a

    Dim var() As Variant

    Dim l As Long

    Dim lRow As Long, lCol As Long



    l = 1



    Set rng = Range("A2:C7")



    ' Create unique list of names

    var = Range("A2:A7")

    On Error Resume Next

    For Each a In var

        arr.Add a, a



    Next

    For l = 1 To arr.Count

        Cells(l + 1, 5) = arr(l)

    Next

    Set arr = Nothing



    ' Create unique list of certificates

    var = Range("B2:B7")

    For Each a In var

        arr.Add a, a

    Next

    For l = 1 To arr.Count

        Cells(1, 5 + l) = arr(l)

    Next

    Set arr = Nothing

    On Error GoTo 0



    ' Ugly code, how to make it more elegant?

    Range("F2").FormulaArray = _

        "=IFERROR(INDEX(R2C3:R7C3,MATCH(1,((R2C1:R7C1=RC5)*(R2C2:R7C2=R1C)),0)),"""")"



    With Range("F2")

        lRow = .CurrentRegion.Rows.Count

        lCol = .CurrentRegion.Columns.Count + 4

    End With



    Range("F2:F" & lRow).FillDown

    Range(Cells(2, 6), Cells(lRow, lCol)).FillRight



End Sub