esProc Desktopesproc-desktop.hashnode.dev·19 hours ago#101 - Deduplication of Row-Based Data - by Whole RowProblem description & analysis: There is a data table: Task: Find out the whole-row data that are not duplicate and the first duplicate whole-row data. Solution: Use SPL XLL and enter the following formula: =spl("=E(?1).group(ID,Name,Gender,Birthday...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 20, 2024#100 — Deduplication of Row-Based Data — by Key ColumnProblem description & analysis: There is a data table: Task: Find out the data that are not duplicates and the first duplicate data in the Name column. Solution: Use SPL XLL and enter the following code: =spl("=E(?1).group(Name).(~(1))",A1:D7) The ...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 15, 2024#97 — Count Duplicates of Simple MembersProblem description & analysis: Here below is a data table of names: Task: Count the number of duplicate names. Solution: Use SPL XLL and enter the formula in cell B2: =spl("=?1.conj().select(~==?2).count()",A$2:A$6,A2) Download esProc Desktop fo...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 14, 2024#96 — Identify Duplications of Row-Based Data — by Key ColumnProblem description & analysis: Here below is a personnel data table: Task: Identify whether there are duplicate data, if so, fill in 1 in the corresponding Dup field, otherwise fill in 0 (the identifying basis is that the data in the Name field are...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 13, 2024#95 — Identify Duplications of Simple MembersProblem description and analysis: Here below is a data table of names: Task: Identify whether there are duplicate names, if so, fill in 1 in the corresponding Dup field, otherwise fill in 0. Solution: Use SPL XLL and enter the formula in cell B2: =s...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 11, 2024#93 — Intersection, Union and Difference in the Case of Row-Based Data — Two Sets — by Key ColumnProblem description: The following tables list the sales data of the top 10 products by sales in January and February: Solutions: Use SPL XLL to tackle the following tasks respectively: A. Find out the sales data of the products that make the top 1...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 8, 2024#92 — Intersection, Union, And Difference in the Case of Simple Members — Multiple SetsProblem description: The following table lists the top 10 products by sales in the first couple of months of the year (the number of months will increase over time): Solutions: Use SPL XLL and deal with the following tasks respectively. A. Find out ...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 7, 2024#91 - Intersection, Union, And Difference in the Case of Simple Members - Two SetsProblem description: The following table lists the top 10 products by sales in January and February: Solutions: Use SPL XLL and deal with the following tasks respectively. A. Find out the products that make the top 10 in both January and February: =...Discuss·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 ...Discuss·10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 5, 2024#89 — Calculate Proportion Using the Aggregation Values of the Data from the Same GroupProblem description & analysis: Here below is an annual and quarterly sales data table: Task: Calculate the proportion of each quarterly sales to the total sales of the year (to calculate in cell D1). Solution: Use SPL XLL and the code is as follows...Discuss·10 likes#Excel-tutorial
