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:
Solved! Go to Solution.
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.
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.