Formula to make a list of only unique Names from column

[tonywatsonhelp]

Hi Everyone,
Hi have a sheet called PDD that has lots of details in it,

I'm creating a dashboard that lets me filter out some data and need to make list of the unique names froma filtered list.

So i'll run a macro to say select the dates and the product type,
and I have a filtered sheet of all this data

now what I need is a list of unique Names rom the data left in Column D (I want to do this to lots of columns but effectively if we do column D I can repeat in as in wish)

If I was righting the formula in English it would go like this:

In Sheets "Cals1" Starting at D2 make a list of all the unique names in sheet PDD range D10:D500 visible cells only.
(a macro might also be ok but it would need to run very quickly as i'll have about 100 of these to do, so a formula will be easier to reproduce.)
any ideas if this can be done and how best to do it?
thanks
Tony

 

[Peter_SSs]

For that sized range I think this should be fast enough. Give it a try.

Sub UniqueList()
  Dim d As Object
  Dim c As Range
 
  Set d = CreateObject("Scripting.Dictionary")
    For Each c In Sheets("PDD").Range("D10:D500").SpecialCells(xlVisible)
      d(c.Value) = Empty
    Next c
  Sheets("Cals1").Range("D2").Resize(d.Count).Value = Application.Transpose(d.Keys())
End Sub

 

[tonywatsonhelp]

Peter,
Absolutely brilliant thank you
Tony

 

[Peter_SSs]

You're welcome.