esProc Desktopesproc-desktop.hashnode.dev·Dec 16, 2024#118 — Group by the Combination of Multiple ColumnsProblem description & analysis: Here below is a data table: Task: We want to group the data by Year and Quarter, and count the total sales of each quarter. Solution: Use SPL XLL and enter the following code: =spl("=E(?1).groups(Year,Quarter;sum(Sale...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Dec 13, 2024#117 — Simple GroupingProblem description & analysis: Here below is a data table: Task: We want to group the data by Class and calculate the average score of each subject for each class. Solution: Use SPL XLL and enter the following code: =spl("=E(?1).groups(Class;avg(Ma...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Dec 9, 2024#113 — Ranking of Row-Based DataProblem description & analysis: Here below is a data table: Task: Calculate the ranking of students by TotalScore (in descending order) and fill the results in corresponding cells of column D. Solution: Use SPL XLL and enter the following formula in...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Dec 4, 2024#110 — Sorting of Row-Based Data — by ExpressionProblem description & analysis: Here below is a data table: Task: Sort in reverse order by the sum of three columns, Maths, English, and PE. Solution: Use SPL XLL and enter the following formula: =spl("=E(?).sort(Maths+English+PE:-1)",A1:E12) The r...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Dec 3, 2024#109 — Sorting of Row-Based Data — by the Combination of Multiple ColumnsProblem description & analysis: Here below is a data table: Task: Sort in reverse order by the order combination of three columns (Maths, English, and PE). Solution: Use SPL XLL and enter the following code: =spl("=E(?).sort(Maths:-1,English:-1,PE:-...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 28, 2024#106 — Delete Data that Can be PairedProblem description & analysis: Here below is a data table: Task: Delete the rows that have the same data in the fields of ID, Name and Item, and whose positive and negative values in Value field can be completely offset. Solution: Use SPL XLL and e...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 27, 2024#105 — Filter by the Number of DuplicatesProblem description & analysis: The following table lists the sales data of the top 5 products by monthly sales: Task: Find out the data of products that appears three or more times. Solution: Use SPL XLL and enter the following formula: =spl("=E(?1...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 6, 2024#90 — Generate Numbers in Each GroupProblem description & analysis: Here below is the student data table: Task: Now we want to create student IDs for every student. The student ID should be class number + student’s serial number in the class, and the serial number should be two-digit ...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 4, 2024#88 — String Concatenation And AggregationProblem description & analysis: There is a data table: Task: Now we want to concatenate the ID numbers for each group of names by | to form a string, and put the result in column C (to calculate in cell C1). Solution: Use SPL XLL and enter the follo...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 1, 2024#87 — Merge Data of The Same GroupProblem description & analysis: Here below is an annual and quarterly sales data table: Task: In this table, column A is ordered and has duplicate values. Now we want to clear the data from all rows in the same group except the first row. Solution: ...10 likesexcel
