Because three different columns (assumed below to be
F) need to be checked, an
OR function looks appropriate:
so that a match for any of the three columns will return
TRUE. Place this in a ‘helper’ column in the first row occupied by data (say Row 1, or adjust
F1 above accordingly).
MATCH checks to see whether, for example, the value of
B1 exists in
keyarray, where that is the given name for the range that contains the list of keywords/key-phrases (~30-40 in number) – need not be in same sheet or workbook but if not the full path must be specified and recommended that the 'other' workbook is open when applying the formula to a new batch of data.
0 forces an exact match only (alternatively
-1 for smallest value that is greater than or equal to
1 for largest.)
MATCH returns the location of the found value in the array (otherwise, with parameter
#N/A). That is numeric so
ISNUMBER tests for a number (any number) – to exclude
Hence provided any of
F1 is in
keyarray the result will be
TRUE – otherwise
To copy the formula down over 25-30 thousand rows conveniently where some contain merged cells, place something (say ‘end’) in the intersection of the last occupied row and the ‘helper’ column (to prevent expanding the size of the spreadsheet unnecessarily). Copy the cell containing the formula, select the cell immediately below it then
Ctrl+Shift+Down/Paste to fill the helper column downwards for all occupied rows, without continuing past last occupied row and with overwriting ‘end’.
Filter on ‘helper’ column for
TRUE, select all occupied columns, copy and paste into new sheet/workbook. Delete blank rows in new sheet/workbook and save. (May choose to delete ‘helper’ column from source also.)
Ensure that when occasionally adding items to
keyarray the named range covers the additions.