I have a Sheet that contains a list of machines with their serial numbers, owner, date of begin of operation, status and many other parameters. I want to filter only the rows that contain the serial numbers I have on a different sheet in a list. I don't want to use the filter function since I want to change two parameters of the specific machines that correspond to the serial numbers in my list (in this case change the status to sold and the date of sale, and leave all other parameters unchanged).
Therefore I tried the Filter Views. I use Filter by condition → Text is exactly and I input as Value or Formula the name of the range I created in a different sheet, or the range itself as
=temporary!$A$1:$A$60
The filter doesn't return a single result this way. If I change the Value or Formula to just one cell:
=temporary!$A$1
The filter works and filters the row that contains the serial number specified in temporary!$A$1.
But this means I have to filter 60 times, one for each serial number. I am pretty sure I am missing something and there has to be a way to reveal only the rows containing any of the serial numbers in my list.
Best Answer
I manage to do this by using "Filter by condition" > "Custom Formula is" and then as Formula we used: =MATCH(X2;range;0) X is the column where the values are against which the filter needs to compare and range is the range to search for the filter criteria. It should always be X2, because the formula searches for the criteria in the row below the header.
This way only the Rows containing the values in range will be filtered.