esProc Desktopesproc-desktop.hashnode.dev·Feb 17, 2025#145 — Insert Blank Row when Meeting with Data ChangeProblem description & analysis: Here below is a data table: Task: We want to insert one blank row when the values in two adjacent rows change, the result is as follows: Solution: Use SPL XLL and enter the following code: =spl("=?1.conj().group@o(~)...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Feb 14, 2025#144 — Insert Row after Specific RowProblem description & analysis: Here below is a data table: Task: Insert one row after the number 100, and fill in a001, a002, … in turn, like this: Solution: Use SPL XLL and enter the following code: =spl("=E@b(?1).group@i(~[-1].#1==100).(~|new(st...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Feb 12, 2025#143 — Expand One Row into Multiple Rows after Splitting TextProblem description & analysis: The following is a data table, in which columns D and E have multiple lines of text, the number of lines is the same, and such lines are in one-to-one correspondence. For example, F corresponds to Fail, as shown below:...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Feb 11, 2025#142 — Expand One Row into Multiple Rows Based on ValueProblem description & analysis: There is a data table, which stores some information of products. The first column is the serial number, and the second column is the remaining quantity. The initial data is as follows: Task: Now we want to copy each ...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Feb 8, 2025#141 — Generate Continuous Arrays — Concatenate Results into a StringProblem description & analysis: The values in columns A and B are natural numbers, representing the start and end points of the array respectively: Task: Use the values in columns A and B to generate a string composed of a natural number array, and ...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Feb 7, 2025#140 — Generate Continuous ArraysProblem description & analysis: There is a column of serial numbers (No), as shown in the figure below: Task: Now we want to use the two numbers in each row to expand into continuous number intervals, like this: Solution: Use SPL XLL and enter the ...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Feb 5, 2025#139 — Interconversion of Rows and Columns in Reverse OrderProblem description & analysis: Here below is a data table: Task: Now we want to transpose the M columns of a two-dimensional table to M rows, and the transposition order should be: the M-th column, M-1 column, M-2…2, 1. The results are as follows: ...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Jan 27, 2025#138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple RowsProblem description & analysis: Here below is a data table: Task: Now we want to convert it to a row-based table, like this: Solution: Use SPL XLL and enter the following code: =spl("=E(?1).pivot@r(Names,Sales;Weekday,Day).new(Names,Sales,Day).sele...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Jan 26, 2025#137 — Re-group or Sort When Filling Grouped Data into ColumnsProblem description & analysis: There is a data table. In this table, the products with the same name may have multiple colors. Task: Convert the data to the form as shown below, that is, arrange each type of product in one row, and then list the Na...excel
esProc Desktopesproc-desktop.hashnode.dev·Jan 24, 2025#136 — Put Data in a Group Horizontally into ColumnsProblem description & analysis: Here below is a data table: Task: Rank the students by score, and arrange the names with the same score into the same row. The result is as follows: Solution: Use SPL XLL and enter the following code: =spl("=E(?1).gr...10 likesexcel sheets
