Problem description & analysis:
The following Excel table has several columns of numbers:
| A | B | C | |
| 1 | 100 | 204 | 200 |
| 2 | 202 | 100 | 102 |
| 3 | 260 | 270 | 108 |
| 4 | 11 | 99 | 100 |
| 5 | 12 | 100 | 100 |
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:
| A | B | C | |
| 7 | 100 | ||
| 8 | 202 | 100 | |
| 9 | 11 | 99 | 100 |
| 10 | 12 | 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