Hello,
I am an excel newb, wondering if excel has built in functionality to do the following.
Turn this data:
Name | Activity | Option
Bob | Monday Activities | Golf
Bob | Tuesday Activities| Kayak
Marge | Monday Activities | Spa
John | Tuesday Activities| Soccer
Liz | Tuesday Activities| Spa
Into:
Name | Monday Activities | Tuesday Activities
Bob | Golf | Kayak
Marge | Spa |
John | | Soccer
Liz | | Spa
Excel will somewhat support what you are looking for in their pivot chart feature which converts data in the format you provided into a custom, user-defined table or chart.
The table will support listing the unique names in the first column and the unique activities in the first row, but for the options, Excel has trouble knowing how to display them and will try converting them to a number as it is possible that more than one option could exist for each name/activity combination.
Finally, some may suggest you use Excel's transpose feature. This is done by selecting the table and copying it. Then, you would right-click on an empty cell and select Paste Special, then select Transpose. This will merely flip the columns for rows which is not what you are looking for.
I'd suggest you go with the pivot table feature, but consider converting the options values into numbers.
Good luck!
Pivot tables allow you to manipulate and display numerical data, not strings such as "kayak", so while you can create a pivot table very easily, it will only count/sum etc. the instances of kayak, soccer etc., not display the values.
You could reformat the data to produce something that gives you an approximation of your desired output:
Name Activity Golf Kayak Soccer Spa Bob Monday 1 Bob Tuesday 1 Marge Monday 1 John Tuesday 1 Liz Tuesday 1(above may not display here correctly, but will show correctly if pasted into Excel using Paste Special>Text)
However, even then you would still have to use a combination of =GETPIVOTDATA and other formulae to achieve a satisfactory result. This could get quite messy due to the very real possibility of errors being introduced into the data (spelling, trailing spaces etc.). Considering this would require you to reformat the data anyway (and if you are doing this you may as well just format it as you desire), I'm guessing this may not be much use to you.
The VBA required to achieve what you want is non-trivial and would depend greatly on the exact layout of your data and what information is already known or could be pulled from elsewhere, e.g. the activities, names. If the dataset is not very large then the effort in reformatting would likely be less than attempting a programmatic solution.