Convert Three Columns to Table

I have three columns "x", "y" and "z" and I want to convert data of these columns to a table where "x" is vertical, "y" is horizontal. My question is: How can have data of column "z" on this table? Is there any formula? (Please see attached excel file)
Thanks in advance for your cooperation.

I can't remember if excel 2013 supports power query, if it does, then that is an alternative option.

Here's the M code for the query, you will need to format the source data as a table first.

Please Login or Register  to view this content.

Originally Posted by jason.b75

Try this formula in F3, then drag right and down to fill the table.

=IF(ROWS(E$3:E3)=COLUMNS($F2:F2),INDEX($C$3:$C$15,ROWS(E$3:E3)),"")

edit:-

It could be done with a pivottable, but the columns would be in ascending order, not the original order. Also it would not work correctly if there are any duplicates in x or y.

Thank you. Your formula as well as works.

Thanks. Your formula is also an answer for my question. It as well as works

Hi Bo-RY

Thanks. Your formula is also an answer for my question. It as well as works.

Thanks. I will test it too.