Problem description & analysis

Below is Excel file book1.xlsx:

If the value of column A is empty, copy the value of column C in the current row to column D of the previous row. Continue such operation in a loop, and then delete the currently empty rows. Below is the expected result:

Solution & explanation

Method 1: Though sequence of sequences

Write the following p1.dfx in esProc: 

Explanation:

A1   Import data from the clipboard as a sequence of sequences.

A2  Handle A1’s sequences in a loop. For the current sequence, if the first member is null, append the value of the third member to the previous sequence and then filter away the sequence where the first member is null.

A3  Convert A2 to a string.

Method 2: Through table sequence

Write the following p1.dfx in esProc:

Explanation:

A1  Import data from the clipboard and add a new column _4; @t option enables reading the first row as column headers.

A2  Handle A1’s table sequence in a loop. For the current row, if the value of the first column is empty, assign the value of the third column to the fourth column in the previous row, and then filter away the record where the first column is empty.

After the script is executed, select cell A3(A2) in esProc, and click “copy data” on the right. Then back to Excel to click cell A8 and press Ctrl+V to paste the result in.

Q & A Collection

https://stackoverflow.com/questions/63790459/check-cut-and-paste-delete-row-move