Creating cross-tabs with pivot tables

Hey folks

Any thoughts on how to simply create cross-tabs in a kind of People who X also Y kind of format? Tried doing it with pivots and getting nowhere. A simple example would be:

excel example.png

Data-set I have has a fair few more variables per person. Anyone got any ideas?

Thanks very much

Nick

Wow, thanks very much, really appreciate you taking the time to help with this.

I've swotted up on array formulas but I still can't figure out how to expand the table if i have more data. Have tried just expanding all the cell refs and using ctrl+shft+ent but makes everything 100%.

Any light you can shed would be fantastic. I haven't sent the sheet as would really like to understand the process so can repeat it myself.

Thanks again

I did a little playing around with simarui's formula and found that if you keep the dimensions" square, as in 3X3 (original) or 4X4 (as you'll see if you double click cell H2 in the attached file) the values in the table are correct. I can't explain the behavior, but thought that it might be useful to know.

Copy of percentage comparison.xlsx

Hope this helps.