Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column Label?

I tried using a pivot table to get summary info and distinct values and this works great. However, I cant seem to get at the Column Labels or Row Labels to get the unique names for a particular Field I'm using which was the point.

I've also tried simply doing the following formula, but this leaves in blank spaces in place of the duplicate values which I don't want:
=IF(COUNTIF(A$2:A2,A2)=1,A2,"")

I've tried this which simply gives me the first cell value only:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)


I tried setting up a NamedRange which consisted of these text values, which is fine, but how would OR do you actually get these text values to populate into a particular cell(s) as you cant simply say =NamedRange in a cell because you get a #VALUE error?

This has to be dynamic as well so that when the source updates (minus duplicates), so does the place I'm putting the list of text values. I've seen where you can reference a table column header and do this, but then the header is present, which isn't very useful and creating an Extract which again only works with static data, so no dice there either.

Any help would be appreciated