Lost as how to turn this into a workable table

[NicBKK]

Dear Gurus,

I receive a report every month 1 tab per month of my 20 companies revenues for our 10 products.
I am lost as to how I could organise this into a table that would be workable for making pivots and dashboards?

Thanks for any help

[lochan79]

How about converting the cross-tab table to a flat table and keep appending monthly data one below another? Since it's a fixed 20x10 table with fixed company & product combination, it will be easy to use an INDEX(monthly_data, match(company,row_range,0),match(product,column_range,0)).

[Bo_Ry]

Please try at

B29:C29
=IF(ROWS(B$29:B29)>ROWS($B$5:$B$24)*COLUMNS($D$4:$M$4),"",INDEX(B$5:B$24,(ROWS(B$29:B29)-1)/COLUMNS($D$4:$M$4)+1))

D29
=IF(C29="","",INDEX($D$4:$M$4,MOD(ROWS(C$29:C29)-1,COLUMNS($D$4:$M$4))+1))

E29
=IF(C29="","",INDEX($D$5:$M$24,(ROWS(B$29:B29)-1)/COLUMNS($D$4:$M$4)+1,MOD(ROWS(C$29:C29)-1,COLUMNS($D$4:$M$4))+1))


or Power Query

Please Login or Register  to view this content.

[NicBKK]

Thanks a lot, exactly what I needed 😊