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

Marzatron

New Member
Joined
Oct 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Marzatron

New Member
Joined
Oct 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Whew, I've solved this by using a totally different formula with (search cell in B1 here):

Excel Formula:
=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 :)
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,275
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top