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.

esProc Desktop is now FREE to download: http://www.scudata.com/download-Desktop