In Excel, Search A Target Value And Hide Columns To Its Right

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:

Explanation:

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.

Comments

[Judith-Excel-Sharing]

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...

[Judith-Excel-Sharing]

Feel free to discuss any Excel problems with experts in our Discord or Reddit communities: discord.gg/hgbKEvJ4 & reddit.com/r/esProc_Desktop/