esProc Desktop Makes Excel Gurus

Find here and fill there in sheets endlessly? Some tasks can be challenging even for Excel experts. Manual work is tedious and error-prone, overtime is a norm.

① Find the max value and its adjacent members

② Concatenate top 3 in each group

③ Rank items within each category

④ Transpose rows to columns for each category

⑤ Split text and expand into multiple rows

These are mainly due to Excel's limitations, and experts, despite knowing many tricks, cannot overcome them.

Programming automate these tasks? It would save time and reduce errors. Well... sounds great! However, ...

VBA has limited capabilities in spreadsheet data processing, making these tasks quite troublesome and not much different from manual work.

Python? It looks powerful, but it's also far beyond your capabilities. Moreover, it can only work outside Excel, resulting in a poor user experience.

Let esProc Desktop Help You!

Write formulas directly in Excel, solving complex tasks in one line.

① Find the max value and its adjacent members

② Concatenate top 3 in each group

③ Rank items within each category

④ Transpose rows to columns for each category

⑤ Split text and expand into multiple rows

Write programs outside Excel, eliminating repetitive tasks.

Split one sheet into multiple sheets by a certain column

Just three lines of code.

A
1=T("s.xlsx")
2=A1.group(Client)
3=A2.(file("r.xlsx").xlsexport@ta(~;~.Client))

Merge 100 xls files and fill the file names into cell values

The merge issue is not a problem at all, still with only three lines of code.

A
1=directory@p("tmp/*.xlsx")
2=A1.conj((fn=filename@n(~),T(~).derive(fn:Commodity)))
3=T("Amount.xlsx",A2)

Grid programming is like writing Excel formulas

Everything you need is here. Say goodbye to overtime

Merge, aggregate and split

  • Merge table with same columns
  • Merge and delete duplicated columns
  • Merge and aggregate duplicated columns
  • Append and accumulate data to summary
  • Split by categories or rows
  • ……

Set operations and belongingness judgment

  • The intersection, union, and difference of simple members
  • The intersection, union, and difference on uncertain number of sets
  • Judge equality and belongingness between sets
  • ……

Special grouping and aggregation

  • Group every N members
  • Group by adjacent data
  • Group by empty or non-empty rows
  • Group by data interval
  • Concatenate same group of data into text
  • ……

Expand and complement

  • Generate continuous intervals
  • Convert one row to multiple rows
  • Split text into multiple rows
  • Fill in missing parts in continuous values
  • Fill up a number of blank row every N rows
  • ……

Searching and filtering

  • Find position of a certain value
  • Filter by location
  • Find the first and last data within each category
  • Find positions of the maximum and minimum values
  • Select the top Nand last N
  • ……

Duplicates judgment, counting and deleting

  • Find duplicate values
  • Count the number of duplicates
  • Remove paired data
  • Deduplicate row-based data
  • Deduplicate without changing original order
  • ……

Association and comparison

  • Association through a table
  • Interval association
  • Use tow-dimensional association table
  • Use interval range to look up association table
  • Associate multiple rows
  • ……

Operations on text and datetime

  • Split a text into multiple
  • Extract number and date
  • Split into words
  • Calculate overlapped time interval
  • Generate consecutive Sundays
  • ……

Calculate cell value and aggregation value

  • Calculate by adjacent rows and intervals
  • Cumulate with possible early termination
  • Calculate cumulative value in categories
  • Add aggregation of same category
  • Split summary into detail rows
  • ……

Sorting and Ranking

  • Align data in specified order
  • Align data with duplicates in specified order
  • Concatenate members of same ranks
  • Sorting in category
  • Ranking in category
  • ……

Conversion between rows and columns

  • Rows to fixed columns
  • Convert row-style table and cross table
  • Convert row-column upper classification
  • Put data in category into columns
  • Re-categorize data when put into columns
  • ……

File extraction and generation

  • Extract data from uncertain rows
  • Extract data from specified cells
  • Extract data from mixed-style structure sheet
  • Fill in specified cells
  • Generate card-style sheets
  • ……
Read the e-book "SPL Programming"
Watch the video "SPL video course" for free
Excel Routine Collection Daily Practice
Free Download esProc Desktop