Transpose sets of columns. Transposing 48 different columns essentially into 4 columns

Question

Hello Experts,

I have a dataset in below format. This data has monthly quantity and value ($) in different columns. Columns with date format as yyyy-mm-dd has quantity in it whereas columns with $ mmm-yy has value($) in it.

There are essentially 48 columns (2 years * 12 months * 2 measures (qty and value$))

Input:

CountryCluster2021-01-012021-02-012021-03-012021-04-012021-05-012021-06-012021-07-012021-08-012021-09-012021-10-012021-11-012021-12-01$ Jan-21$ Feb-21$ Mar-21$ Apr-21$ May-21$ Jun-21$ Jul-21$ Aug-21$ Sep-21$ Oct-21$ Nov-21$ Dec-212022-01-222022-02-222022-03-222022-04-012022-05-012022-06-012022-07-012022-08-012022-09-012022-10-012022-11-012022-12-01$ Jan-22$ Feb-22$ Mar-22$ Apr-22$ May-22$ Jun-22$ Jul-22$ Aug-22$ Sep-22$ Oct-22$ Nov-22$ Dec-22AP                  -                    -                    -                     0                  -                    -                    -                    -                    -                    -                    -                    -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                    -                    -                    -                    -                    -                    -                    -                    -                    -                    -                    -                    -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -  BQ                  -                    -                    -                     0                  -                    -                    -                    -                    -                    -                    -                    -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                    -                    -                    -                    -                    -                    -                    -                    -                    -                    -                    -                    -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -                  -  CR         20,634           5,490         10,206         11,622         12,600         14,640           9,931         16,003         16,637         13,300         13,642           9,918            174               47               87               99            107            125               91            147            153            122            126               91         20,977         15,457         22,081         16,098         18,455         19,995         16,561         17,002         20,127         15,457         17,899         20,703            180            132            189            138            158            171            142            146            172            132            153            177AP         21,649           7,800         23,324         20,066           7,726         15,324         13,599         15,047         15,441         12,228         12,543         11,386         3,618         1,327         3,903         3,227         1,292         2,507         2,236         2,475         2,539         2,011         2,063         1,873         15,568         11,471         16,387         11,947         13,696         14,839         12,290         12,618         14,937         11,471         13,283         15,365         2,304         1,698         2,425         1,768         2,027         2,196         1,819         1,868         2,211         1,698         1,966         2,274BQ         40,499         19,200         25,644         16,380         21,300         23,580         21,050         24,493         25,281         23,231         23,829         22,301         1,743            826         1,114            702            913         1,011            910         1,059         1,093         1,004         1,030            964         32,352         23,838         34,055         24,826         28,463         30,837         25,541         26,222         31,041         23,838         27,604         31,930         1,301            959         1,369            998         1,145         1,240         1,027         1,054         1,248            959         1,110         1,284CR         13,972           5,460           8,856           7,668           6,618           7,608           7,504           7,735           7,591           6,245           6,406           5,631         2,351            950         1,478         1,239         1,144         1,253         1,234         1,272         1,248         1,027         1,054            926           7,523           5,543           7,919           5,773           6,618           7,171           5,939           6,097           7,218           5,543           6,419           7,425         1,114            820         1,172            854            980         1,061            879            902         1,068            820            950         1,099AP         21,780           2,700           9,720         10,260         10,224           8,982           8,353         14,458         13,168         12,159         12,453         10,272            192               24               86               50               49               43               74            128            117            108            110               91         20,746         15,286         21,838         15,920         18,252         19,774         16,378         16,815         19,905         15,286         17,701         20,475            171            126            180            131            150            163            135            138            164            126            146            169BQ         18,984           8,328         13,572           4,632         10,835         13,188         10,669         12,007         12,243           8,099           8,308           7,542         3,212         1,378         2,250            754         1,766         2,150         1,755         1,975         2,013         1,332         1,366         1,240           9,213           6,789           9,698           7,070           8,106           8,782           7,274           7,468           8,840           6,789           7,861           9,093         1,364         1,005         1,435         1,046         1,200         1,300         1,077         1,105         1,308         1,005         1,164         1,346CR         19,849         10,340         12,461           9,230         10,200         11,712           8,554         13,068         13,489         12,395         12,714         11,899            854            445            536            396            437            502            370            565            583            536            550            515         16,973         12,507         17,866         13,025         14,933         16,178         13,400         13,757         16,285         12,507         14,482         16,751            683            503            718            524            600            651            539            553            655            503            582            674AP         13,910           4,092           8,004           4,416           5,400           5,580           6,393           7,120           7,193           5,741           5,888           5,345         2,337            694         1,327            736            893            918         1,051         1,171         1,183            944            968            879           6,774           4,991           7,130           5,198           5,959           6,456           5,348           5,490           6,499           4,991           5,779           6,685         1,003            739         1,055            769            882            956            792            813            962            739            855            989

I want to transpose this 48 columns into four columns (Month, year, Qty and value$) as below:

Output:

CountryClusterMonthYearQuantityValue $APJan2021  BQFeb2021  CRMar2021  APApr2021  BQMay2021  CRJun2022  APJul2022  BQAug2022  CRSep2022  APOct2022

I know I can use multiple transpose tool to achieve this, but want to know the best approach (using combination of transpose and developer tools) to achieve above exercise as sometimes, the transpose tool automatically deselects certain columns which were selected earlier.

Attached is the sample input and output files.

Regards, Ravi

Question_Transpose.xlsx

apathetichell · Accepted Answer

@c_raviramThere seems to be parts of this which aren't in your discussion above - ie multiple values for the same country/cluster for the same period... and what you want to do with zeros? I believe this handles most of what you want.

HomesickSurfer · Accepted Answer

Hi @c_raviram

This works.  See below and attached.

Please 'Like' and 'Accept as Solution' if you agree.