Joining data from unfixed column locations

Hi,

I need help on joining tables where the look-up values are not set in any specific columns, but could be anywhere. How do I join using non-key columns between two tables?

Table 1 is the driving factor table:

Table 2 is where I have to look up the values, but could land in any column:

Table 1 Row 1 can be joined with Table 2 via Column 1 and Column 3

Row 2 can be joined with Table 2 via Column 1, Column 3, and Column 6

Row 2 can also be joined with Table 2 via Column 2, Column 4, and Column 5

Row 3 can be joined with Table 2 via Column 1, Column 6, and Column 7

The desired result is:

Join

Accepted answers

Martyn

Transpose and join is definitely the way forward for something like this -

The summary tools are used to concatenate all of the column values (grouped by key) to ensure that the result only contains record where there was a complete join (i.e. all populated columns from table 1 were found in columns in table 2).

This works from the data you have given.

Joining data from unfixed column locations.yxmd

All comments

BrandonB

You probably will want to use a transpose tool on each table and select the table key as a key field in both tools. Then feed the outputs of both transpose tools into a join tool where you join on the value fields. The inner join will show you all of the values that match between the two keys which can then be used downstream in your process.

Martyn

Transpose and join is definitely the way forward for something like this -

This works from the data you have given.

Joining data from unfixed column locations.yxmd

rjavier

I got stuck after joining the transposed inputs. I didn't know how to loop back the main table. Thank you very much for your help.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335