# esProc Desktop and Excel processing (2021)

## 0. Basic usages

## 1. File extraction and generation

Retrieve data from complex row-based text files

Extract data from uncertain rows

Extract specified cell data in Excel

Extract repeated cell areas in Excel

Extract data of mixed-style structure in Excel

Extract column-direction data in Excel | 2

Extract contiguous areas in Excel

Extract data from XML and JSON

Fill in the specified cell in Excel

Fill in column data horizontally

## 2. Merge, aggregation and split

Merge row-based Excel tables | 3

Combine columns and deal with duplicate data

Remove duplicate and summary columns when merging

Combine tables with different columns horizontally

Remove duplicates when combining rows and columns | 2

Summarize duplicates with combining rows and columns

Fixed format table alignment summary

Append and accumulate data to a summary table

## 3. Searching and filtering

Find Position of a Certain Value

Filter Data by Multi-Condition

Find the First and Last Data | 2

Find the Maximum and Minimum Values

Find and Filter Data by Adjacent Values

Find and Filter Data in Adjacent Intervals | 3

Find Intervals with Continuous Data | 2

Filter Data Categories by Condition of Aggregate Value | 2

Choose One Data in Each Category

Filter Data with Maximum and Minimum Values in a Category | 2

Find Adjacent Rows in the Same Category | 2

Filter Data by the Aggregate Value of Category | 2

## 4. Calculate cell value and aggregation value

Calculate Columns According to Other columns

Calculate Data by Adjacent Rows and Intervals | 2

Calculate Cumulative Value with Possible Early Termination

Calculate Adjacent Rows with Continuous the-Same-Category Data

Calculate Adjacent Rows with Discontinuous the-Same-Category Data

Generate Calculated Columns Based on the Aggregation Results | 2

Calculate Cumulative Value in Categories

Add Aggregation of Same Category | 2

Generate Numbers for the Same Group of Data

Split Summary Values of Groups into the Detail Rows

Fill in the Cell Value by the Position in the Category

Empty the Consecutive Same Data Except for the First Row

Add a Summary at the First Row of Each Group | 3

Summarize Data by Column Direction

## 5. Operation on sets and judgment of belongingness

The Intersection, Union, and Difference of Simple Members | 2 | 3 | 4

Calculate the Intersection, Union, and Difference of Row-Style Data

Calculate the Intersection, Union, and Difference on Uncertain Number of Sets

How to Judge Equality and Belongingness Between Sets | 2

How to Judge Equality and Belongingness Between Sets Regardless of Order

## 6. Judgment, counting and deleting of duplicate data

Count the Number of Duplicates

Count the Number of Duplicates for Uncertain Columns

Deduplicate without Changing Original Order

Deduplicate Multiple Related Columns

## 7. Sorting and ranking

Align Data with Duplicates in Specified Order

Concatenate members with the same ranks

## 8. Special grouping and aggregation

Grouping by Empty or Non-Empty Rows

Concatenate the same group of data into text | 2

Aggregating Uncertain Number of Columns

## 9. Association and comparison

Use Formulas to Perform Association

Associating Tables through a Single Column | 2

Associating Tables through Multi-Columns

Associating a Single Column with Multiple Columns of Another Table

Interval Association in the Formula | 2

Use the Associated Table for Interval Association | 2

Use a two-dimensional association table

Use interval range to look up association table

Associate multiple rows of data

## 10. Conversion between rows and columns

Convert row-style table and cross table

Convert row-column upper classification

Put data in category horizontally into columns | 2

Re-categorize or sort the categorized data when it is put into columns

Convert certain columns of the same row into multiple rows as classification members

Convert every N columns into multiple rows

Convert categories into columns

Convert rows and columns in a category

reverse order of columns after transposing

## 11. Expand and complement

Convert one row into multiple rows based on the value | 2

Split a text into multiple rows | 2

Fill in missing parts in continuous values | 2

Fill up a number of blank lines every N rows

Insert a row after a specific row

Insert a blank row with data changing

Expand multiple columns horizontally

Expand multiple N columns horizontally

## 12. Operations on text

Split a text into multiple | 2

Get multiple texts after Splitting and concatenating

Take out numbers from texts | 2 | 3

Split different types of characters from texts | 2

How to Extract the First Two Words from a White-space-separated String

## 13. Operations on date and time

Summarize date by year and month

Calculate time repeat interval

Generate all dates between two dates | 2

Generate consecutive time points with the same interval

The first Friday of a month/quarter/year

## 14. Mathematical operations and more

Generate permutations and combinations

*SPL Official Website**👉* *https://www.scudata.com*

*SPL Feedback and Help**👉* *https://www.reddit.com/r/esProc_Desktop/*

*SPL Learning Material**👉* *https://c.scudata.com*

*Discord**👉* *https://discord.gg/cFTcUNs7*

*Youtube**👉* *https://www.youtube.com/@esProcDesktop*

*Linkedin Group**👉* *https://www.linkedin.com/groups/14419406/*