How to Remove Common Data of Two Strings from One of Them

 

Task description

There is one column of data in Excel, in which the values are days from Sunday to Monday separated by comma, as shown below:

..

 

The task is to delete data which is also contained in both A1 and A2 from A3, and put the result in A10:

..

Directions:

1. Start esProc and create a new program cellset.

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Open the above to-be-processed Excel file.

3. Select the area of A1:A3 and press Ctrl+C to copy the Excel data.

4. Switch back to esProc, select A1 and press Ctrl+V to paste data in A1:A3. Do not place the caret above A1 in its editing status during the pasting. Below is the cellset with pasted data:

..

5. Write script to do the task:

..

 

Now write the above code in detail and explain it line by line:


A

B

1-3

……(Data   pasted from Excel)


4

=A1.split@c()


5

=A2.split@c()


6

=A3.split@c()


7

=A6\A4\A5


8

=A7.concat@c()


 

A4  Split the text in A1 into a sequence of strings by comma. 

A5  Split the text in A2 into a sequence of strings by comma.

A6  Split the text in A3 into a sequence of strings by comma.

A7  Remove values from A6s sequence that are also contained in both A4 and A5s sequences. 

A8  Use comma to concatenate string members of A7s sequence into a larger string. 

 

6. Execute the script. Then click A8 to view the expected result in the value viewing section on the right:

..

7. Click the button highlighted by the circle in the picture above to copy the result to clipboard.

8. Switch back to Excel, select A10 and paste the result in.

 

 

Attachment】 data.zip