How do I remove set of values from the table? In practice, sometimes we encounter a scenario where it is necessary to remove from the list of entities one or more of the values specified in an ancillary records. For example, in the first list there is a list of workers, and in the second list of workers that need to be removed. How to perform this without having to manually remove one by one from the table?
First, from the initial list, create a structured table. This will be achieved by selecting all the data, and pressing the combination of the CTRL + T key. Then, on the right hand side, we add a column with a formula to determine if the entity is listed as an entity in the supporting records. For this we will use the MATCH function:
Everywhere where the MATCH function returns the result, we get a value different from zero. Otherwise, we receive an error message: #N/A. To make the result of the function more acceptable, we can correct the formula using the IFNA function:
At the end we should replace all numerical values which the formula returns:
The result is a column, we can also call it RESULT, which returns the value of “YES” if the number belongs to the supporting records, and “NO” if it does not belong. Now we just need to filter table to show all of the „YES“ values and further delete them from the table.