"There are multiple groups of data in the following Excel table. Each group has two columns; and .."

jinxing RaqForum 41 No.
176 View • 3 Months ago

In Excel, align every two columns to present them while setting missing values as 0

excel(115)

There are multiple groups of data in the following Excel table. Each group has two columns; and these columns have different lengths. In each group, the 1^st column contains code numbers and there are duplicate code numbers among the groups.

	A	B	C	D	E	F	G	H
1	Mass	10	Mass	11	Mass	12	Mass	13
2	80	22005	81	30908	81	46532	80	22259
3	81	33306	82	47792	82	97559	81	42002
4	82	27314	84	1315498	83	35698	82	233130
5	83	27204	85	110460	84	2391605	84	6892485
6	84	644196	86	25905	86	51365	85	502763
7	85	54723	87	31240	87	34415	86	37660
8	86	28384	88	22801	88	21819	87	40078
9	87	32212	90	24878	89	25326	88	39850
10	88	38615	91	36830	90	30998	89	44535
11	89	13155	92	27189	92	28916	90	38188
12	90	20406	93	29146	93	19224	92	31855
13			95	22505	94	31359	93	18951
14			96	18047	95	22533	94	37879
15			97	97665	96	28238	95	38750
16					97	132143	96	16119
17					98	18459	97	171050
18					99	17121	98	23113
19					101	9579	99	34733
20					102	235454	100	23821
21					103	25700	101	28792
22					104	17415	102	442859
23					105	28789	103	28505
24					106	16005	104	14448
25					107	17534	106	22950
26					108	24548	107	25922
27							108	29475
28							109	15177
29							110	123036
30							111	30295
31							112	18465
32							113	52162
33							114	19906
34							115	64107
35							116	60852

The computing task: put all code numbers in the 1^st column, align the code number field of each group to the 1^st column and display the detail data columns while setting missing values as 0.

	A	B	C	D	E
1	Mass	10	11	12	13
2	80	22005	0	0	22259
3	81	33306	30908	46532	42002
4	82	27314	47792	97559	233130
5	83	27204	0	35698	0
6	84	644196	1315498	2391605	6892485
7	85	54723	110460	0	502763
8	86	28384	25905	51365	37660
9	87	32212	31240	34415	40078
10	88	38615	22801	21819	39850
11	89	13155	0	25326	44535
12	90	20406	24878	30998	38188
13	115	0	0	0	64107
14	102	0	0	235454	442859
15	107	0	0	17534	25922
16	94	0	0	31359	37879
17	99	0	0	17121	34733
18	112	0	0	0	18465
19	91	0	36830	0	0
20	104	0	0	17415	14448
21	109	0	0	0	15177
22	96	0	18047	28238	16119
23	114	0	0	0	19906
24	101	0	0	9579	28792
25	106	0	0	16005	22950
26	93	0	29146	19224	18951
27	111	0	0	0	30295
28	98	0	0	18459	23113
29	103	0	0	25700	28505
30	116	0	0	0	60852
31	95	0	22505	22533	38750
32	108	0	0	24548	29475
33	113	0	0	0	52162
34	100	0	0	0	23821
35	105	0	0	28789	0
36	92	0	27189	28916	31855
37	110	0	0	0	123036
38	97	0	97665	132143	171050

Use SPL XLL to enter the following formula:

=spl("=d=E@b(?), f=(d.fno()\2-1).(~*2+1).(~/$[,]/ (~+1)),d.select(#1).pjoin@f( #1,#1,#2; ${f.(replace@s($[d.select(#N),#N,#M],$[ N,M],~)).concat($[;])}).(~.array().(ifn(~,0)))",A1:H35)

Picture3png

$[…] represents a string; it is used to avoid escaping with two quotation marks in Excel. E@b reads an Excel table without column headers. pjoin@f performs a full join. select(#1) selects records where the 1^st column field isn’t null. replace@s performs multiple matches and replacements. array()converts a record into a string. ifn() returns the 2^nd parameter when the 1^st parameter is absent.

Source:https://stackoverflow.com/questions/78370163/how-do-you-combine-multiple-column-pairs-into-one-table-with-zeros-for-missing

SPL Official Website 👉 https://www.scudata.com

SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/

SPL Learning Material 👉 https://c.scudata.com

Discord 👉 https://discord.gg/cFTcUNs7

Youtube 👉 https://www.youtube.com/@esProcDesktop

Linkedin Group 👉 https://www.linkedin.com/groups/14419406/

excel(115)

Desktop