RaqForum 28 No.
340 View  • 
     ...

Find Column Names According to Column Values

 
excel(115) locate calculation(2) position-based retrieval(1)

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.

Q & A Collection

 https://stackoverflow.com/questions/63849461/excel-find-the-columns-first-and-last-cell-entry-and-use-that-columns-positions


excel(115) locate calculation(2) position-based retrieval(1)
 
 •  340 View  •