I have been trying to figure this out for days, but I just cannot get everything to work. I can get some scenarios to work, but not all at the same time. It is very bazaar and frustrating. Even ChatGPT can't get it right.
Please download and look at the workbook in the attached zip file. It will make what is needed much easier to understand.
Setup:
- I have a worksheet called "Libraries" with a listing of sample libraries, with columns for (A)Vendor, (B)Library Name, (C)Library Subgroup, and (D)Patch Name.
- I have a "Report" worksheet where I am doing filtering on the data in the Libraries sheet.
- Ok, on the Report sheet I have 3 cells, C11 to E11 which are for inputting text to filter by. C11 filters over column Libraries!B, D11 over Libraries!C, and E11 over Libraries!D. The filter restricts the result listing more as more of cells C11 to E11 get text to filter on, as in the resulting list has to contain only records from Libraries that match all of the specified text as per the corresponding column. I hope that makes sense.
- Also on the Report sheet is cell C13 which is for specifying filter text which should be a match if ANY of the 3 columns in Libraries contains the specified text.
- Finally on the Results sheet is the cell (C17) containing the filter formula, which becomes the first row of a column of the resulting filtered list. There are other columns that are similar but for simplicity just focus on getting C17 to work putting correct results in that column. Also, ignore the other text cells on the Report sheet.
This formula below (in cell C17) seems to work for every scenario of these inputs except when there is text only in C13, in which case it is like that text is ignored - I basically just get as the result the entire list as if no filter text has been specified.
But if I change the '+' before the IF to a '*', then the lone text in cell C13 works, but other scenarios do not.
The formula I have now:
=IFERROR(
FILTER(
Libraries!B2:B500000,
(
( ISNUMBER(SEARCH(LOWER(Report!C11), LOWER(Libraries!B2:B500000))) *
ISNUMBER(SEARCH(LOWER(Report!D11), LOWER(Libraries!C2:C500000))) *
ISNUMBER(SEARCH(LOWER(Report!E11), LOWER(Libraries!D2:D500000))) )
+
IF(Report!C13<>"",
( ISNUMBER(SEARCH(LOWER(Report!C13), LOWER(Libraries!B2:B500000))) +
ISNUMBER(SEARCH(LOWER(Report!C13), LOWER(Libraries!C2:C500000))) +
ISNUMBER(SEARCH(LOWER(Report!C13), LOWER(Libraries!D2:D500000))) ) )
) > 0
),
""
)
If you can solve it you will be forever known as a genius.
PLEASE SOLVE IT!
Thanks.
Bookmarks