Renaming headers

Question

Hi all, so I am looking at some of the prep for the advanced certification and came across this question, I am having trouble with renaming the headers for each country and then unioning them all together, I was thinking of a macro but I can't seem to get it to work. Would anyone have a solution to the question?

Food+Staples.xlsx

Food+staples.PNG

Yallah365 · Answer

Hi Luke, thanks for the reply so I did get it through your way sort of, but I was just wondering if there was some way to do it with a macro where it will look at the two rows for each country and have those headers and data and then union them all by name in the end. So I was thinking of taking the first two rows and then using a dynamic rename tool to get the headers so that I can get the headers and data for the first two rows, I am just wondering if there is a way to continue this, so the next run will take rows three and four and then have all the headers unioned by name.

Food+staples.yxmd

Luke_C · Answer

Hi @Yallah365

I think it will be valuable for you to come up with the solution, but to get you started I think the way I'd tackle this is:

* Text to columns to split out the data (appears to be a mix of hyphen to start + commas for the actual data), may need 2 tools.
* Transpose the data by country/header
* Join up region
* Use filters+summarize tools+formulas as needed to answer the question