Dynamic Matrix - need to map several attributes then find rows that fulfill all criteria

[pohrt]

Hello, 

need some ideas on how to solve this issue. I have a table with a changing amount of attributes in columns.

The rows have values assigned to these column attributes. Now my second input is something like an "order".

Step 1: I need to compare each attribute in the column with the specific order and tick when I find a match.

Step 2: I need to find the row that has ALL orders and then need to find out in which store I can buy the order.

(outcome can also be none or more than one)

To demonstrate I created a simple example:

Table 1 lists all attributes (trousers, shirt, scarf, shoes). The rows then contain which Shop seels what

Input 2 is the list of things the customer wants to buy.

The Number and headers of these attributes can vary

* I would like to now "tick" all shops that sell green trousers, then long shirt sleeves etc

* at the end I need to find all or none shop that sells everything I want to buy

Any efficient ideas?

Thanks so much already

Petra

[pilsworth-bulien-com · Accepted solution]

Hello @pohrt 

I've given your question a go. Here's how I got on.

First of all, I input both your tables 1 and 2, standardised the case using the data cleansing tool, and then gave a Record ID to both tables. 
Here's table 1:

Here's Table 2:

After that, I pivoted the data, and joined on all values where both the header (i.e trousers) and value (i.e green) matched the request in table 2.

This next part is what allows you to change the number of input columns, without causing any issues. I use a count records to count the number of items requested. Then I count the number of matched per original row, from table 1. If these two counts match, then it means that the original row from table one, contains everything requested. 

Finally, I join back to the original table to format the results column as you displayed in you image. 

I hope the above helps, I've attached the workflow with annotations below. 

Please let me know how you get on.

Regards - Pilsner

[HannahBrauer223]

This was my thought too! Transpose everything so you have a column with "Type" and a column with "Color". Then match from there. This answer is a much more detailed version. 

Hannah Brauer

Crowe, LLP