USE Excel to transpose ONLY unique column into a header row

Question

I am trying to transform a spreadsheet in SHEET1 that looks like this:

COLUMN1
A 
A 
A 
B 
B 
B 
B 
C 

I want it to be in SHEET2 like this

ROW Header: A B C

I am basically trying to:

Get unique items in first column Transpose and promote these items as column headers

I need the output table in SHEET 2 to dynamically update each time. No VBA. for example if I add D to SHEET 1, D will automatically update on SHEET 2 Header.... A B C D

COLUMN1
A 
A 
A 
B 
B 
B 
B 
C 
D

I want it to automatically update in SHEET2 like this

ROW Header: A B C D

Answer 1

Would a formula like this work for you? It takes all the values of the column in sheet 1 and transposes the unique values. Putting this formula in sheet 2 will automatically update a header as the values in sheet 1 change.

=TRANSPOSE(UNIQUE(FILTER(Sheet1!A2:A500,Sheet1!A2:A500<>"")))

EDIT: This formula will only work for those with Excel O365 and later since the newer versions allow for more dynamic formulas.

Answer 2

Try INDEX/AGGREGATE:

=IFERROR(INDEX($A:$A,AGGREGATE(15,3,ROW($A$5:$A1000)/((COUNTIF($A$1:A$1,$A$5:$A$1000)=0)*($A$5:$A$1000<>"")),1)),"")

If column (you wrote row) headers start from A1 then first header you should enter manually in A1, then in B1 enter formula and copy to the right.