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