To cope with complicated or unusual calculations that hard to implement in Excel, we can copy the source data to esProc using clipboard function and compute it in esProc. Compared with the traditional add-ins, esProc clipboard is easy to deploy and smooth to operate.

Here’s the workflow. You select and copy an area in Excel and use esProc function to read data from system clipboard. esProc will calculate the data, return result to the system clipboard (or you can copy the multiple returned results manually), and finally paste the result to Excel. There are several types of scenarios, which I’ll explain in the following part.

Basic uses

esProc clipboard() function is responsible for achieving relevant basic uses. I’ll illustrate this using an example.

Below is the source Excel, where column A contains student names, column B-D contains scores of math, English and physics.

	A	B	C	D
1	name	math	english	physics
2	lily	97	100	99
3	Joshua	100	99	100
4	Sarah	98	99	96
5	Bertram	94	95	85
6	Paula	91	88	91
7	Sophia	92	81	76
8	Ben	87	80	76
9	Ruth	92	91	87
10	Pag	95	87	87

The task is to get the 3 students for each subject whose scores rank in top 3 and append their names after each subject.

To get the task done, we need a series of functionalities, including TopN and join by sequence numbers. It’s hard to do this in Excel. But it’s easy with esProc. Select the source data area A1:D10 in the Excel worksheet, press Ctrl+C to copy it to the system clipboard, and open esProc IDE to write and execute the following script:

	A		B
1	=clipboard().import@t()		/ Import data from clipboard
2	=A1.top(-3;math).(name)		/ Get the students whose math scores rank in top 3
3	=A1.top(-3;english).(name)
4	=A1.top(-3;physics).(name)
5	=join@p(A2;A3;A4).export()		/ Join student names into a two-dimensional table and then export them as strings
6	=clipboard(A5)		/ Write strings to the clipboard

The script uses clipboard function in two ways. When calling the function without a parameter, it returns strings from the clipboard, as A1 does. When calling it with the parameter being a variable or a cell name, it writes strings to the clipboard, as A6’s clipboard(var) does.

After the script is executed, select cell B11 in the Excel worksheet and press Ctrl+V to paste data in the clipboard to B11-D13, as shown below:

	A	B	C	D
	…	…	…	…
10	Pag	95	87	87
11		Joshua	Lily	Joshua
12		Sarah	Sarah	Lily
13		lily	Joshua	Sarah

Consistent clipboard copy

There’s a problem. During editing and debugging the script, it’s natural that we probably use the copy & paste multiple times and the clipboard content will be changed again and again. When we execute clipboard() the next time, it will return the most recent content. Then we need to re-copy the source data from Excel. It’s a trouble for the computation.

To get rid of the trouble, esProc provides @e option to work with clipboard function to always return the data first copied from Excel. Here’s how it works.

In the script of getting students whose scores rank in top 3 for each subject, we copy A3 and A4 to B2 and B3 respectively. Then we execute the script and get error report in A2 because the clipboard() function in A1 retrieves data wrongly. Now we use clipboard@e() in A1 and switch code the same way again, as shown below. The script can be correctly executed.

	A	B	C
1	=clipboard@e().import@t()	/ Import data from clipboard
2	=A1.top(-3;math).(name)	=A1.top(-3;english).(name)	=A1.top(-3;physics).(name)
3	=join@p(A2;B2;B3).export()	/ Join student names into a two-dimensional table and then export them as strings
4	=clipboard(A3)	/ Write strings to the clipboard

Multiple result sets

clipboard(…) can return only one result. But sometimes there are more than one calculation task to be performed on the same area of data.

We can copy cell values (variable values) from esProc’s value viewing section to the clipboard and paste them to Excel manually.

Here’s an example.

The task: After scores of each subject append names of students whose scores rank in top 3 in this subject; add a new column target to contain the three students who have the nearest total scores for everyone as their targets to outperform. Not all students have 3 targets and the target students should be connected by the greater than sign (>).

Select the source data area A1:D10 from Excel and press Ctrl+C to copy it to the system clipboard. Then open esProc IDE to write and execute the following script:

	A	B	C
1	=clipboard@e().import@t()	Import data from clipboard
2	=A1.top(-3;math).(name)	=A1.top(-3;english).(name)	=A1.top(-3;physics).(name)
3	=join@p(A2;B2;C2)	Data area 1: top 3 students in each subject in terms of scores
4	=A1.derive(sum(math,english,physics):subtotal)	Total scores of each student
5	=A4.derive(t=subtotal,A4.select(subtotal>t):beforeMe)	3 students whose scores are directly before each student
6	=A5.new(beforeMe.top(3;subtotal).(name).concat(">"):target)	Data area 2: 3 students whose total scores are directly before each one

A3 stores data area 1 that holds the top students in each subject for their scores. A4 stores data area 2, which contains each student’s targets to outperform. export()and clipboard() are not needed here.

Now we return the results to Excel. Click on A3 that stores data area 1 and then the “Copy data” icon in the value viewing section on the right, as shown below:

 

Select B11 in Excel and press Ctrl+V to paste data area 1 to B11-D13, as shown below:

	A	B	C	D
1	name	math	english	physics
2	Lily	97	100	99
3	Joshua	100	99	100
4	Sarah	98	99	96
5	Bertram	94	95	85
6	Paula	91	88	91
7	Sophia	92	81	76
8	Ben	87	80	76
9	Ruth	92	91	87
10	Pag	95	87	87
11		Joshua	lily	Joshua
12		Sarah	Sarah	lily
13		lily	Joshua	Sarah

Click on A6 that holds data area 2, and press Shift while click the “Copy data” icon, then select E1 in Excel and press Ctrl+V to paste both detailed data and column headers in the area to E1:E10:

	A	B	C	D	E
1	name	math	english	physics	Target
2	Lily	97	100	99	Joshua
3	Joshua	100	99	100
4	Sarah	98	99	96	lily>Joshua
5	Bertram	94	95	85	Sarah>lily>Joshua
6	Paula	91	88	91	Bertram>Sarah>lily
7	Sophia	92	81	76	Pag>Ruth>Paula
8	Ben	87	80	76	Sophia>Pag>Ruth
9	Ruth	92	91	87	Bertram>Sarah>lily
10	Pag	95	87	87	Ruth>Paula>Bertram
11		Joshua	lily	Joshua
12		Sarah	Sarah	lily
13		lily	Joshua	Sarah

To control whether to copy column headers or not through the Shift key is a unique esProc feature.

Multiple source data areas

How about if there are multiple data source areas in Excel but when clipboard can only hold the most-recently copied area?

esProc has its solution. It supports copying the clipboard content directly to a cell in the cellset.

I’ll illustrate this through an example.

There are two Excel sheets, order details and employees.

The order details:

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	1	WVF  Vip	1	440	2014-11-03
3	2	UFS  Com	1	1863	2015-01-01
4	3	SWFR	2	1813	2014-11-01
5	4	JFS  Pep	2	671	2015-01-01
6	5	DSG	1	3730	2015-01-01
7	6	JFE	1	1445	2015-01-01
8	7	OLF	3	625	2015-01-01
9	8	PAER	3	2490	2015-01-01

The employee information:

	A	B	C	D	E	F	G
1	EId	State	Dept	Name	Gender	Salary	Birthday
2	2	New  York	Marketing	Ashley	F	11001	1980-07-19
3	3	New  Mexico	Sales	Rachel	F	9000	1970-12-17
4	4	Texas	HR	Emily	F	7000	1985-03-07
5	5	Texas	R&D	Ashley	F	16000	1975-05-13
6	6	California	Sales	Matthew	M	11000	1984-07-07
7	7	Illinois	Sales	Alexis	F	9000	1972-08-16
8	8	California	Marketing	Megan	F	11000	1979-04-19
9	1	Texas	HR	Victoria	F	3000	1983-12-07

The task: Find the records during the recent number of days, which is defined by the external parameter days (Different values can be passed through it for each execution. The value of 30 means querying order records during the past 30 days); and those for the specified department, which is defined by the external parameter depts in the format of like ["Marketing","Finance"]. The required columns are OrderID, OrderDate, Amount, Dept, and Name. The computing goals involve dynamic query and multi-key query. They are hard to implement in Excel but are easy with esProc.

We select the order details area in Excel and copy & paste both the data and column headers to cell A1 in esProc’s cellset script. Similarly, we select and copy the employee information area to cell A2 in esProc’s cellset script. The following picture shows the results:

 

Note: You should perform the copy & paste actions at the editing status in A1; otherwise the clipboard content will occupy a large stretch of cells in the script, as shown below:

 

This will affect the overall code layout.

The correct operation will show only a small part of the copied data in both A1 and A2. You can only view the whole data by clicking on them. That’s a distinctive characteristic of the cellset coding, which is suitable to copy a lot of data at once without affecting the reading efficiency and code layout.

Now we move on to the script editing, during which we can execute the script for any times we like and perform the clipboard copy& paste manually. Here’s the final script:

	A	B	C
1	OrderID Client…	=A1.import@t()	/Orders list
2	EId State…	=A2.import@t()	/Employee list
3	=B1.switch(SellerId,B2:EId)		/Join with EId
4	=A3.select(OrderDate>=after(date(now()),days*-1)||  depts.pos(SellerId.Dept))		/Query data
5	=A4.new(OrderID,OrderDate,Amount,SellerId.Name:Name,SellerId.Dept:Dept)		/Return desired fields

After the computing task is finished, we can copy A5’s result on Excel using “Copy data” icon. Clear up data in both A1 and A2 before saving the script, otherwise all source data will be saved.