Create a pivot from multiple consolidation ranges with more than one row?

Question

I have two ranges with data like this:

I want to create a Pivot Table that consolidates the data from both tables. However when I do this using ALT+D+P I end up with Country as Row label, with no way to change it's rows (the table fields only say Row, Column, Value and Page1):

What I would like to do is to have it like this (Group Cities by Country and see subtotals):

Is it possible to do this with Pivots from multiple consolidation ranges? If not is there another way to do this?

Edit

I tried doing this with microsoft query, however when I create a new sheet in my workbook, click on add data from Microsoft Query and select the same Excel I have open I get the error "Unrecognized database format". I noticed this error doesn't happen if I add another Excel file.

Answer 1

Great tips on Pivot Tables can be found on Contextures.com, and about multiple consolidation ranges on this page specifically. Quoting from that page:

If possible, move your data to a single worksheet, or store it in a database, such as Microsoft Access, and you'll have more flexibility in creating the pivot table.

These days, you have other options in Excel. If you have Excel 2010 or higher you can use Power Query to combine data from different sheets, workbooks, databases, web, whatever, into a single data source that you can then use as the pivot table source data.

Power Query is a free add-in from Microsoft and runs with Excel Professional Plus. The business grade editions of Excel 2016 have it built into the Data ribbon under Get & Transform.

Answer 2

Consolidation from multiple ranges automatically uses the leftmost column as the row. You can get around this:

Make the City column the leftmost, make a page field that is set to "Men" for the men's range and "Women" for the women's range. Then use grouping to combine the results for each country if you want to.