Problem description & analysis

Below is Excel file book1.xlsx:

Data in column A is divided into blocks by empty row. Each block contains N rows plus one empty row. We are trying to add a new column B. Strings of N rows in each block will be connected by hyphens and entered in the empty rows while the other rows in column B will remain empty. The desired result is as follows:

Solution

Write the following script p1.dfx in esProc:

Explanation:

A1   Import data from the clipboard and split it into a sequence by carriage return.

A2  Create a new group if the previous member is a null string and then remove the null string from each group.

A3  Join members of each group into a string with the hyphen -, count members and add empty strings of same number before the pieced “string”.

After the program is executed, select cell A3 in esProc and click the corresponding “copy data” button on the right. Back to Excel to click cell B1 and press Ctrl+V to paste the result in.

Q & A Collection

https://stackoverflow.com/questions/63754291/combining-cell-values-with-vba