1 Manipulating data for Excel ★

 

0.Basic usages

0.1 Read and write files

0.2 Calculation by clipboard

0.3 Use Add-in in Excel

0.4 General table operations

0.5 Process big files

0.6 Execute SQL on files   2

1.Extract and Fill in

1.7 Extract data from complex row-style text

1.8 Extract data from uncertain rows

1.9 Extract specified cell data

1.10 Extract repeating cell areas

1.11 Extract mixed-style structure

1.12 Extract data in the column direction

1.13 Extract contiguous areas

1.14 Extract data from XML and json

1.15 Fill in the specified cell

1.16 Generate multiple card

1.17 Fill in column data horizontally

2.Combine and Split

2.18 Combine tables with same columns

2.19 Combine columns and deal with duplicate data

2.20 Combine tables with summarizing duplicate columns

2.21 Combine tables with different columns

2.22 Remove duplicates with combining rows and columns

2.23 Summarize duplicates with combining rows and columns

2.24 Fixed format table alignment summary

2.25 Append and accumulate to a summary table

2.26 Split by category or number of rows

3.Find and Filter

3.27 Find the location of a value

3.28 Filter data by multi-condition

3.29 Filter data by location

3.30 Find the first and last

3.31 Find the maximum and minimum

3.32 Choose the top N and last N

3.33 Find and filter by adjacent values

3.34 Find and filter in adjacent intervals

3.35 Find intervals by continuous data

3.36 Filter out categories based on summary value conditions

3.37 Choose one for each category

3.38 Filter with maximum and minimum in a category

3.39 Find adjacent rows in the same category

3.40 Filter by category summary value

4.Calculate cell and Summarize value

4.41 Calculate columns by some columns

4.42 Calculate data by adjacent rows and intervals

4.43 Calculate cumulative value with possible early termination

4.44 Calculate adjacent rows with continuous same data

4.45 Calculate adjacent rows with not continuous same data

4.46 Summarize data with same columns

4.47 Calculate cumulative value in category

4.48 Add a summary column based on columns

4.49 Generate numbers for the same group of data

4.50 Split subtotal values into detail rows

4.51 Fill in the cell value by the position in the category

4.52 Blank the consecutive same data except the first row

4.53 Add a summary row on top of each group

4.54 Summarize data by column direction

5.Set operation and Containing judgment

5.55 Intersection,Union,Difference of simple member

5.56 Intersection,Union,Difference of row-style data

5.57 Intersection,Union,Difference between uncertain numbers of sets

5.58 Set equality and containing judgments

5.59 Set equality and containing judgment with regardless of order

6.Find duplicates, counting and removal

6.60 Find duplicates and counting

6.61 Counting repetitions

6.62 Counting repetitions for uncertain columns

6.63 Deduplicating for row-style Excel

6.64 Deduplicating for simple data

6.65 Deduplicating without changing original orders

6.66 Filter by repetitions

6.67 deduplicating with related columns

6.68 Removal with paired data

7.Sorting and ranking

7.69 Aligning in specified order

7.70 Aligning with repeated values in the specified order

7.71 Concatenate members with the same rank

7.72 Sorting in the same category

7.73 Ranking in category

7.74 Shuffle orders

8.Special grouping and aggregation methods

8.75 Every N members as a group

8.76 Grouping with adjacent data

8.77 Grouping by empty or non-empty rows

8.78 Grouping by data interval

8.79 Concatenate the same group of data into text

8.80 Aggregating with uncertain columns

9.Association and comparison

9.81 Handle association by formulas

9.82 Single column associating another table

9.83 Multi-column associating another table

9.84 Single column associating multiple columns of another table

9.85 Interval association in the formula

9.86 Use association table for interval association

9.87 Use a two-dimensional association table

9.88 Use the interval range to look up the association table

9.89 Associate multiple rows of data

9.90 Associate with detail table

9.91 Compare and find changes

10.Convert rows and columns

10.92 Row to fixed column

10.93 Convert row-style table and cross table

10.94 Convert row-column upper classification

10.95 Put data in category horizontally into columns

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

10.97 Convert certain columns of the same row into multiple rows

10.98 Convert every N columns into multiple rows

10.99 Convert categories into columns

10.101 Convert rows and columns in a category

10.102 Reverse order of columns after transposing

11.Expansion and Complement

11.103 Generate continuous intervals

11.104 Convert one row into multiple rows based on the value

11.105 Split a text into multiple rows

11.106 Complement missing parts in continuous values

11.107 Complement a number of blank lines every N rows

11.108 Insert a row after a specific row

11.109 Insert a blank row with data changing

11.110 Expand multiple columns horizontally

11.111 Expand multiple N columns horizontally

12.Processing text

12.113 Split a text into multiple

12.114 Get multiple texts after splitting and concatenating

12.115 Take out numbers from texts

12.116 Take out date from texts

12.117 Split different types of characters from texts

12.119 Parse key-values from texts

13.Calculate date and time

13.120 Summarize date by year and month

13.121 Calculate time repeat interval

13.122 Generate all dates between two dates

13.123 Generate consecutive time points with the same interval

13.124 Generate consecutive Sundays

13.125 The first Friday of a month/quarter/year

14.Math operations and others

14.126 Dynamic association operation

14.127 Generate permutations and combinations

14.128 Calculating expression

More Excel processing and calculation examples…