Need help converting Excel Data

[ShrikantPatil]

Hello,

I have an Excel where few metrics are reported for various locations for each month.

I would like to covert it into a Table as shown below, Can someone help?

[mceleavey]

Hi @ShrikantPatil ,

I'm sure we can help, but can you please post the data rather than a picture of the data?

Thanks.

M.

[ShrikantPatil]

Thank you for your willingness to help, I appreciate it. I have attached the file to this post. Please not you may see some blank rows which are left intentionally blank to add future month records.

One thought I am having is create Named ranges in Excel and then import them one at a time and append the data to output file. Please let me know if you have other solutions in mind.

Thanks,

Shrikant

[Yoshiro_Fujimori · Accepted solution]

@ShrikantPatil ,

My solution is attached. I hope it may help.

In summary;

1. Break the sheet to three tables with the same format.

2. Union them

3. Remove null rows 

    * The second column was a little tricky at a first glance, as it contained unusual character (non-break space).

4. Set MIS and Location using Multi-Row Formula tool.

5. Rename the columns with Dynamic Rename tool.

[ShrikantPatil]

Thank you very much Yoshiro-san! It worked exactly as I needed.