Problem description

Excel file book1.xlsx contains data as shown below:

We are trying to recode UNIQUE ID field. The rule is like this. Same IDs have same UNIQUE IDs. The expected result is the right part in the following table:

The task involves post-grouping record handling and the use of clipboard.

Directions

1. Write the script:

A1   Import data from the clipboard. @t enables reading the first row as column titles.

A2   Group A1’s data by ID, loop through each group to assign the current group number to variable a, and then loop through each record of the current group to assign a to UNIQUER ID.

A3   Convert A1’s table sequence into a string to put it onto the clipboard. @t option enables outputting the column titles at conversion.

2. Select area A1:B20 in the Excel file and press Ctrl+C to copy it to the clipboard.

Back to esProc to run the program, and then go back to the Excel file, click E1 and press Ctrl+V to paste the result in.

Q & A Collection  

https://stackoverflow.com/questions/63797394/continuous-serial-number-generation-incl-duplicate-values