excel - How to convert matrix to 4-column table without using VBA (Data - PivotTable and PivotChart Report) -

i need convert excel matrix "a" table "b", not using vba. preferably 'reverse pivot', 'unpivot', 'flatten', 'normalize'... picture of have , need receive there similar topics, couldn't find exact 1 interested in. here described looking for, done in vba: excel macro(vba) transpose multiple columns multiple rows , here without vba, 1 column kept , repeated: convert matrix 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

focussing on 2 sets of quantities, essence of want transform table "a" n columns , 3 rows table "b" 3*n rows , 1 column.

a couple of helper columns left (or right) of table "b" can used identify row , column of "a" match each of rows in table "b". these helper columns like

row col   1   1  2   1  3   1   ...       n   1  1   2  2   2  3   2   ...  n   2  1   3  2   3  3   3   ...  n   3 

starting initial row containing pair (1,1) straightforward task create couple of =if() formulae pair in next row which, if copied down, generate remaining pairs of row , column values.

once generated, these (row, col) pairs provide practically needed generate rows of table "b".

the customer , product code columns in table "b" derived using =index(tableacol,row) tableacol represents corresponding column in table "a" eg $b$3:$b$13 customer. quantity column generated using =index(tableaquantities, row, col) tableaquantities range of n rows , 3 columns in table "a" containing quantity values. month column requires range of 3 cells arranged contiguous row or column containing values of "january", "february", "march" , generated in table "b" using =index(months, col) months represents 3 cell range.

there alternative way of achieving same result without explicitly adding helper columns. uses =row() function calculate data row number each row of table "b" , uses in further formulae calculate 2 values of corresponding (row,col) pair. approach leads cumbersome , repetitive formulae generating table "b". if there no reason avoiding helper columns use them - represent simpler , more understood approach.