0

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.

5
  • 1
    VBA should be no problem, formulas might be doable with various combinations of UNIQUE TRANSPOSE FILTER and/or XLOOKUP. If you are still stuck after using these, then come back and edit your question with where you are stuck. Good luck.
    – gns100
    Apr 27, 2021 at 15:47
  • Can you add an auxiliary column with a,b,c,d... and pivot the new table?
    – Lee
    Apr 28, 2021 at 9:43
  • @gns100 Sorry, I'm not sure what you mean by that? I'm completely new to excel, so I don't really know that those functions do. At this point i'm sort of befuddled why this is so hard.
    – Lunkers
    Apr 28, 2021 at 12:14
  • If you are new to excel, then I suggest the semi-manual way of filtering your existing data, then copy-paste that result to a new worksheet. Repeat that for every category. If yo are interested in learning formulas type in those that I gave you and see what they do. In the end, we can only give general advice to general questions.
    – gns100
    Apr 28, 2021 at 21:20
  • Yes, your advice was very helpful! I ended up moving to google sheets, since their functions have stable names despite OS language. With that, i managed to solve the issue by using the filter function!
    – Lunkers
    Apr 29, 2021 at 8:04

1 Answer 1

0

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

enter image description here

1
  • Thank you, this was exactly what i wanted!
    – Lunkers
    May 5, 2021 at 13:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .