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
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.