Microsoft Excel: pivot without aggregation

Using Excel (office 365) on mac, I want to group data by a specific column value, as in a pivot table, but without aggregating it. As an example, i would like to transform:

A 2 B 1 A 3 B 4 

to:

A B 2 1 3 4 

I've tried using pivot tables, but those seem to require some form of aggregation, which I don't want. I only want to list these values.

1 Answer

With your data in M1:N4:

P1: =TRANSPOSE(UNIQUE($M$1:$M$4)) P2: =FILTER($N$1:$N$4,$M$1:$M$4=P$1) 

Select P2 and drag/fill right to Q2