TRANSPOSE OR NOT TO TRANSPOSE? How to change my table to a list format?

Hello,

I have a table with a matrix: rows showing Region, Country, Group and number of product for each country. Product and product code are in columns.
I need to change it to a list view.

Please see attached excel: the ORIGINAL tab shows how the data looks now and the NEW Tab how the new list view should look like.

Please help!

Using column G in the Original sheet as a helper, put zero in G3 and this formula in G4:

=IF(COUNT(D4:F4)=0,"-",COUNT(D4:F4)+G3)

Copy this down to give a cumulative number of records required. Then you can use these formulae in the cells stated on the NEW sheet:

A2: =IFERROR(INDEX(ORIGINAL!A:A,MATCH(ROWS($1:1)-1,ORIGINAL!$G:$G)+1),"")

(Copy this into B2 and C2)

D2: =IF(C2="","",INDEX(ORIGINAL!$D$3:$F$3,MOD(ROWS($1:1)-1,3)+1))

E2: =IF(C2="","",INDEX(ORIGINAL!$D$2:$F$2,MOD(ROWS($1:1)-1,3)+1))

F2: =IFERROR(INDEX(ORIGINAL!$D:$F,MATCH(ROWS($1:1)-1,ORIGINAL!$G:$G)+1,MOD(ROWS($1:1)-1,3)+1),"")

Copy these formulae down as far as you need to.

Hope this helps.

Pete

Hi Pete,

I tried to use your formula and it worked on my sample document but it did not work on my real file. Is there any count of columns that I need to be aware of if I want to paste the formula to another file? I got the number of cumulative records required but then I got only 3 records of the NEW view and the formulas kept on repeating the 3 records over and over when I pulled down the formula.

Thanks,
J.