1
votes

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
2
What version of Excel do you have?BigBen

2 Answers

2
votes

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.

2
votes

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.

enter image description here