Renaming headers

[Yallah365]

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?

[Luke_C]

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:

1. 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.
2. Transpose the data by country/header
3. Join up region
4. Use filters+summarize tools+formulas as needed to answer the question

[Yallah365]

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.