Transpose columns of data in rows

Hi,

I have a list of data where sales are by weeks across columns and stores are in rows. I want to be able to transpose columns of data in rows. is there a excel add in to transpose those column data in rows? For each store I have to copy the store for 52 times as there are 52 weeks of data. Then I am using a VLOOKUP and MATCH function to copy the amount in the rows. Is there a more efficient way to create a list of unique records in rows or transpose the data in rows? Please see attached excel sheet for my problem. Thanks.

Original Data
Store Week 1 Week 2 Week 3 Week 4
Danforth 49,457 50,278 48,173 54,061
Eglinton 212,394 206,661 213,128 197,135

Expected Format
Store Week Amount
Danforth Week 1 49,457
Danforth Week 2 50,278
Danforth Week 3 48,173
Danforth Week 4 54,061
Eglinton Week 1 212,394
Eglinton Week 2 206,661
Eglinton Week 3 213,128
Eglinton Week 4 197,135

Have you found the Copy and Paste Special Transpose check box? That does transpose your data but not to your exact desired result.

Yes, I can use the copy paste special transpose but there is over 1000 stores and 52 weeks, that means I have 5200 rows of data to copy and paste. So, it is not efficient in this problem. Can someone help please.

I attached a sample file for you.

In my opinion.

first of all, define the name useing offset formula, just in case the input data added in sheet

for ex)
Name : Original_Data_Table
=OFFSET('Original Data'!$A$1,1,0,COUNTA('Original Data'!$A:$A)-1,COUNTA('Original Data'!$2:$2))

and use index and match formula

look at the attached file

If any way to easy to get, let me know.

have a nice day