Merging 2 Tables Into 1 Table

[PhantomJoe]

I have 2 tables that I am trying to figure out a less painful way of merging into a single table or identifying some other method to link the 2 tables.

I currently have 2 tables that report on supplier information. Table 1 shows by supplier what category or categories that supplier supports.
There are 9 different categories that a supplier can support with a column indicating yes or no as to whether that supplier supports that category.
Here's an example of the headers for that table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Supplier Name[/TD]
[TD]Category[/TD]
[TD]Yes/No[/TD]
[/TR]
</tbody>[/TABLE]

The 2nd table is a supplier table that shows what sites that supplier supports.
Here's an example of the headers for that table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Supplier Name[/TD]
[TD]Site[/TD]
[/TR]
</tbody>[/TABLE]


In the Sites table a supplier can support 200 or more sites (so naturally there would be a row for each site per supplier).

The goal is to use this data to quickly see what suppliers need to receive requisitions. The order in which it would be determined would be to first filter on the site to find which suppliers support that site and then filter down by category to see which suppliers support both the site and the category. And this file needs to be simple enough that I can hand it off.

At the moment it's 2 different tables on separate worksheets (in the same workbook) and it requires manually filtering to the site (in the Sites table) to get the supplier list and then cross-referencing that list against the category table to find the population that need to receive the particular requisition.

I'm hoping there's a way to either link these tables together or to identify some process that I've not thought of to make this search more automated.

Any help is appreciated.

 

[cerfani]

I made a small example... maybe it works for you...

{=IF(NOT(ISERROR(MATCH(1,(H1=A:A)*(I1=B:B)*("Yes"=C:C),0))),IF(NOT(ISERROR(MATCH(1,(H1=E:E)*(J1=F:F),0))),"OK","NOPE"),"NOPE")}

(this is an array formula and you need to press Ctrl+Shift+Enter, it will create the surrounding {} do not type those)

it first makes sure the company handles the category and then make sure the company serves the site and if both true, it prints "OK"

Note: H1:J1 are the inputs for the company you are checking

 

[PhantomJoe]

I really appreciate the response but this doesn't work from a process standpoint. Meaning that the team I hand this off to won't be checking individual suppliers to see if they service a site and category. What they will be doing is creating a list of suppliers that meet their criteria. So instead of checking every single supplier 1 at a time I'd like to find a solution that allows the user to filter on a specific value in the Sites table and based on that returned list of suppliers then show them which suppliers support what categories.

 

[PhantomJoe]

Shameless bump.

 

[SpillerBD]

You can merge with a Pivot Table using the Data Model or you can Merge the tables using Get & Transform.

 

[PhantomJoe]

You can merge with a Pivot Table using the Data Model or you can Merge the tables using Get & Transform.

I had initially tried combining both worksheets into a single Pivot Table using the data model but I don't know if it's operator error or an issue with the way the tables/data are arranged on the different worksheets but I could not build a Pivot Table that was meaningful.

Also, I'm operating on Excel 2013 to Get and Transform is not available.

 

[SpillerBD]

Make both ranges tables using CTRL+T, and give the Tables meaningful names.
You can use the Data\Connections command to add each Table to the Data Model.
Select a blank sheet, use Insert\Pivot Table command and make sure you select the Use External Data source, Choose Connection and look for the This Workbooks DataModel.
Then choose the fields to drop into the Pivot Table. When you add from a different Table of the Data Model, you should get a relationship warning. The window may not read very clear, but your unique list should be on the lower selection. IF you get an error message about not having unique values, you may need to create a third table that list the items 1 time only and add that table to the model. It will serve as an intermediary table.