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

[c_raviram]

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:

Country	Cluster	2021-01-01	2021-02-01	2021-03-01	2021-04-01	2021-05-01	2021-06-01	2021-07-01	2021-08-01	2021-09-01	2021-10-01	2021-11-01	2021-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-21	2022-01-22	2022-02-22	2022-03-22	2022-04-01	2022-05-01	2022-06-01	2022-07-01	2022-08-01	2022-09-01	2022-10-01	2022-11-01	2022-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-22
A	P	-	-	-	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
B	Q	-	-	-	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
C	R	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	177
A	P	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,274
B	Q	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,284
C	R	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,099
A	P	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	169
B	Q	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,346
C	R	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	674
A	P	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:

Country	Cluster	Month	Year	Quantity	Value $
A	P	Jan	2021
B	Q	Feb	2021
C	R	Mar	2021
A	P	Apr	2021
B	Q	May	2021
C	R	Jun	2022
A	P	Jul	2022
B	Q	Aug	2022
C	R	Sep	2022
A	P	Oct	2022

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  

[apathetichell · Accepted solution]

@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 solution]

Hi @c_raviram 

This works.  See below and attached.

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