cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iamtrangdoan Regular Visitor
Regular Visitor

Using a keyword in Table 1 to find duplicates in Table 2 and show what and how many are available

Hi everyone,

 

I'd like to compare 2 tables using 2 columns Engine Model & Keyword to see which and how many Vendors can repair certain engines or when a keyword is chosen, which and how many Vendors show up.

 

Here is my data:

 

1. "Data" table (contains only 1 Vendor name. With the engines and keywords this Vendor has, how many Vendors in the full updated ALL PARTS table can repair the same engine or show the same keyword and who are they)

Vendor NamePart ReceivedNet Order CostEngine ModelKeyword
Ha822822-4-005$489.21W500EEC
Ha822822-4-005$562.23W500EEC
Ha819300-9-009$578.55W500EEC
Ha822822-4-005$814.00W500EDU
Ha822822-4-005$814.00W500EDU
Ha822822-4-005$814.00W500EEC
Ha819710-2$814.00W200EEC
Ha822825-7-009$945.05W500VALVE
Ha819300-9-009$1,000.00W500FUEL CONTROL
Ha817962-8-012$1,215.00W500DIVIDER
Ha822825-7-009$1,215.00P6FUEL CONTROL
Ha822825-7-009$1,215.00W300EEC
Ha025277-300-08$1,215.00W500FUEL CONTROL
Ha822825-6-009$1,215.00W200DIVIDER

 

2. full updated ALL PARTS table (has all vendor names and their corresponding data)

Vendor NamePart ReceivedNet Order CostEngine ModelKeyword
AMETEK91020-000-4$22,885.00W150EEC
AMETEK91020-000-4$2,382.00W150EEC
AMETEK91020-000-4$1,629.00W500EEC
AMETEK3044926-01$2,389.00W500EEC
ATECH3122633-01$22,885.00W500FUEL CONTROL
ATECH3072344-01SB35301$21,830.00W500DIVIDER
ATECH3073555-01$18,200.00W500FUEL CONTROL
ATECH3072344-01SB35301$2,389.00P6FUEL CONTROL
BUDNEY3108467-01$22,885.00W500FUEL CONTROL
BUDNEY3052959-01$2,547.96P6FUEL CONTROL
BUDNEY3024121$6,777.00W300EEC
BUDNEY3048972-01$9,146.49P6FUEL CONTROL
BUDNEY30J2411-01$22,885.00W300EEC
BUDNEY3123769-01$15,680.09W300PMU
CHAMPION3048972-01CH92043-3P6EEC
CHAMPION3045846-01CH92051-4W300EEC
CHAMPION3045846-01CH92043-3W300EEC
CHAMPION06R30J2078-01CH92043-3W300EEC
CHAMPION06R30J2078-01CH92043-3W300EEC
CHROMA45033752663106862-01W300EEC
CHROMA45033752663118471-01P6EEC
CHROMA45033772613121253-01W300EEC
CHROMA4503366757$6,777.00W300EEC
Ha809575-2-004$18,200.00W500EEC
Ha766859F$6,914.00W500EDU
Ha822835-5-108$6,777.00W500EDU
Ha819300-9-009$6,321.00W300EDU
CRANE9-361-06$7,073.11W300EDU
CRANE9-361-06$14,482.70W300EDU

 

Ex:

 

Using Engine Model from Data table as a slicer, when W500 is selected, how many alternative vendors from the full updated ALL PARTS table can repair that same engine other than Vendor Ha and who are they?

 

Desired results should show a list of the following: AMETEK, ATECH, BUDNEY, instead of showing: AMETEK, ATECH, BUDNEY, Ha. Meaning Ha should be excluded in the list.

 

How would you approach my problem?

 

Thank you so much and please help a newbie out!

 

5 REPLIES 5
IvanDonev Frequent Visitor
Frequent Visitor

Re: Using a keyword in Table 1 to find duplicates in Table 2 and show what and how many are availabl

Just an idea on top of my mind:

  1. Create a custom column that concatenates Vendor Name, Engine Model and Keyword. This will give you a "unique" key for the row. Do that for both tables.
  2. Use Merge As New for the two tables in Query Editor. Use the Data table as first table in the Merge and full updated ALL Parts as a second table in the merge. In the merge type (drop down menu) choose Right outer.
  3.  Filter in all NULL values in the first column (the surogate key column, created in step 1). This will keep only the vendors that do not exist in the DATA table. After you do that, in the resulting table expand all the columns you need from "full updated ALL PARTS" table.
  4. Create a custom column in the Data table, that is concatenation between Engine Model and Keyword. Do the same with the new table from step 1 to 3. Then create a relationship between those columns.

This way you will produce a table, that is essentially containing only the "alternative" vendors, as it excluded the vendor for that particular engine/keyword, which is in the first table. And at the same time it is related to the Data table, so you can have a primary vendor (coming from the Data table) and list of all Alternative ones, using the relationship.

 

HTH, Ivan

iamtrangdoan Regular Visitor
Regular Visitor

Re: Using a keyword in Table 1 to find duplicates in Table 2 and show what and how many are availabl

Hi @IvanDonev, can you please explain what you meant by creating a custom column that concatenates Vendor Name, Engine Model & Keyword? Sorry I'm not really familiar with all this stuff. Thanks!

IvanDonev Frequent Visitor
Frequent Visitor

Re: Using a keyword in Table 1 to find duplicates in Table 2 and show what and how many are availabl

In Query Editor, add a Custom Column with the following formula:

[Column 1] & [Column 2] & [Column 3]

 Just replace [Column 1], [Column2], etc. with your actual columns. The result for step 1 should be "HaW500EEC" for the first row in the Data table.

 

trdoan Member
Member

Re: Using a keyword in Table 1 to find duplicates in Table 2 and show what and how many are availabl

@IvanDonev In Step 4, after creating the custom column, did you mean to move on to step 2 or something. Sorry I don't quite get it because there is already a concatenated column between the Engine Model & Keyword. What's left to concatenate to repeat Step 1? Thank you so much!
IvanDonev Frequent Visitor
Frequent Visitor

Re: Using a keyword in Table 1 to find duplicates in Table 2 and show what and how many are availabl

@trdoan, you need that to create relationship between the new table and the Data table. If you use the first concatenated column, you will never have any relation/matching, because of the Vendor column.