Need help - multiple columns to rows

Question

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.!

Store sales records.xlsx

FinnCharlton · Accepted Answer

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 Answer

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.