Using VBA to create a unique list.

Try this code

Sub Unique_List()
    Dim Rng As Range
    Dim Cel As Range
    Dim Coll As New Collection
    Dim I As Integer

    With Worksheets("Weekly Archives")
        Set Rng = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
    End With
    
    On Error Resume Next
    For Each Cel In Rng
        Coll.Add Cel.Value, CStr(Cel.Value)
    Next Cel

    For I = 1 To Coll.Count
        ActiveSheet.Cells(I + 1, "H").Value = Coll(I)
    Next I
End Sub

Originally Posted by myobreportguru

Thank you Yasser that did the trick.

You're welcome. Glad I can offer some help
Thank you very much for Mr. Bakerman for his great and awesome solution

To speed up things a bit.

Sub Unique_List2()
    Dim sn, x0, j As Long
    
    With Sheets("Weekly Archives")
        sn = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
    End With
    With CreateObject("scripting.dictionary")
        For j = 1 To UBound(sn)
            If sn(j, 1) <> vbNullString Then x0 = .Item(sn(j, 1))
        Next
        ActiveSheet.Range("H2").Resize(.Count) = Application.Transpose(.keys)
    End With
End Sub