# Find Column Names According to Column Values

#### Problem description & analysis

In the following Excel file, the first row in area D1:I6 contains column headers; in the other rows, some cells are empty but cells that are non-empty are always continuous, as shown below:

 A B C D E F G H I 1 4.5.2020 5.5.2020 6.5.2020 7.5.2020 8.5.2020 9.5.2020 2 data data 3 data data data data data 4 data data data data data 5 data data data data data data 6 data data data data

The task is to list the start column and the end column respectively in column A and column B for each row of continuous cells, as shown below:

 A B C D E F G H I 1 start   date end   date 4.5.2020 5.5.2020 6.5.2020 7.5.2020 8.5.2020 9.5.2020 2 4.5.2020 5.5.2020 data data 3 4.5.2020 8.5.2020 data data data data data 4 5.5.2020 9.5.2020 data data data data data 5 4.5.2020 9.5.2020 data data data data data data 6 6.5.2020 9.5.2020 data data data data

The algorithm involves locate query and position-based data retrieval.

#### Solution

Select D1:I6 in the Excel file, copy data in the area to cell A1 in the esProc script, and write code in the other cells:

 A 1 …(Data   copied from Excel) 2 =A1.split@n("\t") 3 =A2.to(2,).new(~.pselect(~!=""):start,~.pselect@z(~!=""):end) 4 =A3.new(A2(1)(start):'start   date',A2(1)(end):'end date')

After the script is executed, copy A4’s result as well as the column headers to cell A1 in the Excel file.

A2: Split A1’s string into a sequence of sequences.

A3: Get detailed data, which starts from row2, to locate the position of the first non-empty member from left to right and that of the first non-empty member backwards for each row.

A4: Get values from the sequence of column names according to positions obtained by A3.