I have the following spreadsheet:
country | year | percent | isRight | isCenter
Austria | 2016 | 35.1 | T |
Austria | 2016 | 21.3 | |
Austria | 2016 | 11.3 | | T
Austria | 2016 | 11.1 | | T
Brazil | 2016 | 28.28 | T |
Brazil | 2016 | 11.3 | | T
Brazil | 2016 | 25.3 | | T
Brazil | 2016 | 4.5 | | T
... | ... | ... | ... | ...
I have this data going back many years with many countries - often coming up every 4 years or so. I'd like to convert this data to the following format:
country | year | right | center
Austria | 2016 | 35.1 | 25.3
Brazil | 2016 | 28.28 | 41.1
... | ... | ... | ... | ...
So here I am:
- Ignoring any cells where isRight and isCenter are both empty.
- Grouping by country and year.
- Summing the percentage of isRight and isCenter and adding them to the new columns: right and center.
Any pointers to how I could do this in Excel without having to do it manually?