# Enhance Excel Calculations by Clipboard ★

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.

Heres 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 Ill explain in the following part.

### Basic uses

esProc clipboard() function is responsible for achieving relevant basic uses. Ill 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. Its hard to do this in Excel. But its 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 A6s 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

Theres a problem. During editing and debugging the script, its 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. Its 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. Heres 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 esProcs value viewing section to the clipboard and paste them to Excel manually.

Heres 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 students 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.

Ill 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 esProcs cellset script. Similarly, we select and copy the employee information area to cell A2 in esProcs 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. Thats 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. Heres 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 A5s 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.