In Excel, Identify Data Layers Correctly and Convert Them to a Standardized Table

#beginners #programming #tutorial #productivity

Problem description & analysis:

Data in the column below has three layers: the 1st layer is a string, the 2nd layer is a date, and the 3rd layer contains multiple time values:

     A
1   NAME1
2   2024-06-03
3   04:06:12
4   04:09:23
5   08:09:23
6   12:09:23
7   17:02:23
8   2024-06-02
9   04:06:12
10  04:09:23
11  08:09:23
12  NAME2
13  2024-06-03
14  04:06:12
15  04:09:23
16  2024-06-02
17  12:09:23
18  17:02:23

We need to identify the three layers of data correctly and convert them to a standardized table:

      D         E          F
1   NAME1   2024-06-03  04:06:12
2   NAME1   2024-06-03  04:09:23
3   NAME1   2024-06-03  08:09:23
4   NAME1   2024-06-03  12:09:23
5   NAME1   2024-06-03  17:02:23
6   NAME1   2024-06-02  04:06:12
7   NAME1   2024-06-02  04:09:23
8   NAME1   2024-06-02  08:09:23
9   NAME2   2024-06-03  04:06:12
10  NAME2   2024-06-03  04:09:23
11  NAME2   2024-06-02  12:09:23
12  NAME2   2024-06-02  17:02:23

Solution:

Use SPL XLL to type in the following formula:

=spl("=E@1(?).(if(ifstring(~):s=~, if(ifdate(E(~))):d=~; [s,d,~])).select(ifa(~))",A1:A18)

SPL returns an integer for the date data. You need to format it into an easy-to-read form through Excel’s "format cells" option (or through SPL’s E() function). Use the same way to handle the time data.

As shown in the picture below:

Explanation:

E()function converts a value to the Excel date/time data; E@1 converts a multilayer sequence to a single-layer one. ~ represents the current member; if() function judges whether it is a string and whether it is a date from left to right and executes the expressions, and then executes the default expression. ifa() judges whether the variable is a sequence.