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!
