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:

undefined 


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:

undefined 

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:

undefined 


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.