In Excel, Align Every Two Columns to Present Them While Setting Missing Values as 0

Judith-Excel-Sharing - Jun 3 - - Dev Community

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

table with code entered

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.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .