Stop a search/filter formula based on two columns from duplicating a row that has a hit in both columns

[Marzatron]

Hi everyone!

I have two reports that I merge based on a unique identifier from Report A (database) which is normally present in Report B (ticket system report), but not always. The Merge ends up with two columns with employee names (one from Report A and one from Report B). From this merged table I want to extract a report that shows all Report A items and Report B items that have an employee's name, as they can be different.

I use the below formula pasted into the same amount of columns as the Merged report and in more than as many rows as I think are needed for any one person's report, around 250, then I hit CTRL+T to make that an Excel table. I use a search cell to type an employee's name to give me their report, in the example below that is C1933. The array shown has 1s for the name columns and 0s for the rest as there is a column that contains notes and often has names in it that I don't want used in this search.

=IFERROR(INDEX(Merge, SMALL(IF(ISNUMBER(FIND($C$1933, FILTER(Merge[[NameA]:[NameB]],{1,0,0,0,0,1},""))), MATCH(ROW(Merge), ROW(Merge)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)), "")

The problem I'm having is that if the name in the query cell C1933 is found in both the NameA and the NameB columns (which is the case for many rows) it will show that row twice in the employee's report. I need to search for their name in both columns as a ticket (B) may be assigned to a person different to who is in charge of the database report record (A). I want that hit to show up in both of their merged reports, but not twice in each of their reports.

I hope someone can help out! I can create an example spreadsheet later if that helps.

 

[Marzatron]

Whew, I've solved this by using a totally different formula with (search cell in B1 here):

=FILTER(Merge,((ISNUMBER(SEARCH(B1,FILTER(Merge,{1,0,0,0,0}))))+(ISNUMBER(SEARCH(B1,FILTER(Merge,{0,0,0,0,1})))))^1,"")

This formula filters rows from the Merge table if there is a hit from the search cell B1 in either NameA or NameB columns without duplicating any rows that have a hit in both.

It searches for the contents of B1 in the NameA column and then in the NameB column (SEARCH) and gives gives a two column array of matches (using ISNUMBER to get TRUE for hits). Then it adds the columns together to give one column containing 0s for no hits in row, 1 for single and 2 for double hits in each row which FILTER then uses to spill any hits from the Merge table.

Hope someone finds this useful