In EXCEL, I would like to change the array of a two columns table (A1:A91;B1:B91) to a 13 X 7 table as follows, by removing the "Turnover" text and replacing the cells with the turnover quantity.

I would like to use some functions such as WRAPCOLS, CHOOSEROWS and TOROW

CodePudding user response：

You can use this formula:

=LET(d,A1:B26,
dOnly,FILTER(d,INDEX(d,,2)<>"Turnover"),
years,UNIQUE(FILTER(INDEX(d,,1),INDEX(d,,2) = "Turnover")),
months,UNIQUE(INDEX(dOnly,,1)),
HSTACK(VSTACK({""},years),VSTACK(TRANSPOSE(months),WRAPROWS(INDEX(dOnly,,2),ROWS(months)))))

CodePudding user response：

In cell D1 put the following formula:

=LET(rng, A1:B26, colA, INDEX(rng,,1), colB, INDEX(rng,,2),
 values, FILTER(colB, colB<>"Turnover"),
 codes, FILTER(colA, colB="Turnover"),
 months, TEXT(EDATE(1, SEQUENCE(1,12,0)),"mmm"),
 matrix, WRAPROWS(values, 12), 
 VSTACK(HSTACK("",months), HSTACK(codes, matrix))
)

and here is the output:

It is just to select conveniently the information we need from the input data to generate it in the format we want. The months since they are all the same we took: 1, i.e. 1/1/1900 and format it via TEXT as mmm. If you need the months for a given year as dates data type and format in Excel as mmmm. Then you can use one of the following options:

• Use the formula: EDATE(x, SEQUENCE(1,12,0)), where x represents any date corresponding to January of a given year or use A2 if it is a date in previous formula.
• Use the formula: TOROW(UNIQUE(FILTER(colA, colB<>"Turnover"))).