Problem description & analysis:
The following Excel table has several columns of numbers:
Task: With a given parameter, find the first same number in each row and hide the columns on its right; if the number does not exist in a row, just hide the whole row. Below is the result when the given parameter is 100:
Solution:
Use SPL XLL to enter the formula below:
=spl("=?1.(~.to(~.pselect(~==?2))).select(~!=[])",A1:C5,100)
As shown in the picture below:
select()function gets members meeting the specified condition. pselect() function gets the positions of the eligible members. to() function gets the first N members. ~ represents the current member.
The formula is used in scenarios where the table has unstandardized data, such as there are missing values in rows/columns and the rows/columns do not have fixed lengths. If there is more than one 100 in a row, columns on the right of the first 100 will be hidden by default. Use pselect@z if you need to hide columns on the right of the last 100.
Top comments (2)
Please free to download it and explore the data processing journey on your own⬇️
SPL download address: scudata.com/download-Desktop
Plugin Installation Method: c.scudata.com/article/1652061135502
References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...
Feel free to discuss any Excel problems with experts in our Discord or Reddit communities: discord.gg/hgbKEvJ4 & reddit.com/r/esProc_Desktop/