Grouping rows by multiple values

Question

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?

Answer 1

You can do this pretty quickly without VBA if you're willing to jump through a few hoops, though if you're going to do this repeatedly I'd recommend a VBA solution.

Start by making a new column that concatenates Country and Year like so:

=country&";"&year

And I'd copy that to column to a new worksheet (or a new area on the existing worksheet - wherever you want your new list to be). Then, do a Remove Duplicates on that list. After you remove duplicates, do a text-to-columns with a semicolon delimiter. Then, sort first by year, then by country. This gets you your unique list of country and year, and groups them in your desired fashion.

From there, it's just a couple of sumifs formulas. With your new list of unique country & year records,

=sumifs(original percent column, original country column, country, original year column, year, original right column, T

And so on. It'll look kind of like:

and: