How to Remove Common Data of Strings from One of Them

Task description

The Excel sheet below contains one column of data. Each of its values is weekdays separated by comma, as shown below:

undefined

 

The task is to delete data which is also included in both A1 and A2, and puts the result in A10:

undefined

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:

undefined

5.      Write script for doing the task:

undefined

 

Now let’s list the above code 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 A6’s sequence that are also contained in both A4 and A5’s sequences.

A8   Use comma to concatenate string members of A7’s sequence into a larger string.

 

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

undefined

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

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

 

 

Reference data.zip