I am having a terrible time trying to display some key/value pairs in Excel as columns a specific way.
Note: I have found a way to accomplish this in Python so this question is mostly for the sake of curiosity
I have a small data set consisting of key/value pairs:
Person | Item |
---|---|
Steve | Apple |
Steve | Orange |
Bob | Potato |
Bob | Grape |
Bob | Cherry |
Bob | Orange |
Bob | Watermelon |
Max | Cherry |
Max | Apple |
Max | Potato |
Max | Orange |
My goal is to simplify the data and show it with the “persons” (keys) as labels, and remove duplicates:
Person | Steve | Bob | Max |
---|---|---|---|
– | Apple | Potato | Cherry |
– | Orange | Grape | Apple |
– | – | Cherry | Potato |
– | – | Orange | Orange |
– | – | Watermelon | – |
So far, the closest I’ve gotten is to make everything a pivot table, and set both fields as Rows, with the first one being “Person”.
https://i.stack.imgur.com/x3nC4.png
This sorts the items under the correct person, and I would “just” need to split those in seperate columns. This seems really simple, yet I cannot figure out how. I am fairly certain this can easily be accomplished with VBA but I wanted to find a way using Excel’s default tools.
Any help is appreciated!