In Excel, Align Every Two Columns to Present Them While Setting Missing Values as 0
Judith-Excel-Sharing
Posted on June 3, 2024
Problem Description & analysis:
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 1st 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 1st column, align the code number field of each group to the 1st 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
Solution:
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)
As shown in the picture below:
Explanation:
$[…] 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 1stcolumn field isn’t null. replace@s performs multiple matches and replacements. array()converts a record into a string. ifn() returns the 2nd parameter when the 1st parameter is absent.
Posted on June 3, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
June 3, 2024
October 22, 2021