Need help - multiple columns to rows

[Bo_Zhang]

Hi Community,

I am stuck on a problem and would like to know if anyone has some ideas.

I have a set of sales records from different stores, and I consolidate those into one sheet (see the Source Data tab) through macro.

Then, to have further analysis, I need the data laid out by store, location and year (see the Target tab). 

I tried transpose; however, the location and amount were treated separately, and it was hard to distinguish which data was for which year.

Thanks in advance.!

[FinnCharlton · Accepted solution]

Hi @Bo_Zhang , here is a solution that should work for any number of store names, using a batch macro:

Here is inside the macro:

[Christina_H · Accepted solution]

If possible, I would recommend changing the process you're using to consolidate the data so that you get cleaner data.  However, the attached gives the output you're looking for.

[Bo_Zhang]

Thank you, @FinnCharlton and @Christina_H! Both solutions worked perfectly.