Search of multiple values connecting multiple tables

[ema_rz]

Hello everyone.

I have an issue with managing multiple tables. I’m not practical with Power Query or VBA, so I hope some of you can give me a hand to solve this.

I have 6 tables. The goal is to return the link between one column of the last table and one column of the first one. There are multiple results in every step.

e.g. To column R corresponds multiple value of column Q, values from column Q are present multiple time in column O and to them correspond multiple value of column N. values in column N are present multiple times in column K and to them correspond multiple value of column L. Value from column L are present multiple time in column I and correspond to multiple values of column H. Values from H multiple times in E and multiple correspondences to F. Values from F multiple times in C and multiple correspondences to B.

The final result should be: given a value from column R, what values from column B are linked to it?

I hope I’ve made it clear.

Thanks a lot.

P.S. I've tried to use a formula but I cannot made it into multiple steps: =TEXTJOINT("; ";TRUE;UNIQUE(IF(S2=Tab6[ColR];Tab6[ColQ];"")))

[#2]

Here's an example of how it should work. 

[#3]

@ema_rz 

Here is a horrible formula (name in B19):

=IFERROR(TEXTJOIN(";";TRUE;FILTER(Table2[Column1];ISNUMBER(MATCH(Table2[Column2];FILTER(Table3[Column2];ISNUMBER(MATCH(Table3[Column1];FILTER(Table4[Column1];ISNUMBER(MATCH(Table4[Column2];FILTER(Table5[Column2];ISNUMBER(MATCH(Table5[Column1];FILTER(Table6[Column1];ISNUMBER(MATCH(Table6[Column2];FILTER(Table7[Column1];ISNUMBER(MATCH(Table7[Column2];B19;0)));0)));0)));0)));0)));0))));"")

You'll have to replace the names of the tables and columns.

[#4]

@Hans Vogelaar Unfortunately it doesn't work properly.

[#5 · Accepted solution]

@ema_rz Now, I didn't recreate your tables, 6 levels deep, but did a mock-up at a smaller scale in Power Query. Although you mentioned not to be "practical" with PQ, you might be able to follow the steps in the attached (not so elegant) example. With a number of consecutive merge and expand steps, starting with the last table, you will end up with the sequence of items from the first column in the first table that correspond to the item selected from the second column of the last table.

[#6]

@ema_rz 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? And explain where the formula fails? Thanks in advance.

[#7]

@ema_rz 

What a revolting problem.  Are you sure you need to solve it?

To iterate through a sequence of Tables you would need to be able to address each Table by index, say = TblColλ(1,2), where the Lambda function is defined by

= LAMBDA(tbleNum,fieldNum,
      CHOOSECOLS(CHOOSE(tbleNum,Table1,Table2),fieldNum)
  )

(note the presence of a beta release function).

Then you would need a further Lambda function = NextTargetλ(target,k), which, given the list of valid target values for Table k would produce a list of valid output values

= LAMBDA(tgt,tblNum,
      DROP(REDUCE("", tgt,
         LAMBDA(list,t,
            VSTACK(list, FILTER(TblColλ(tblNum,1),TblColλ(tblNum,2)=t)))
         ),
      1)
   )

Even then, you would need to provide a further REDUCE formula to step through the Tables and cater for the fact that some of your tables have the columns in reverse order.  Concatenating the result, if you ever got there, would be the easy part!

Unless someone is paying you a large sum of money for this, I would recommend finding some other way of spending you life.  I attach a file with outline ideas.

[#8]

Thanks for the effort, I totally agree with the last part of your response! I'll follow your advice!

[#9]

@ema_rz 

Having been, shall we say, somewhat discouraging, I used @Riny_van_Eekelen 's setup to scan through the array of tables, picking up a selection at each stage.  I always searched column 2 to return column 1 (array calculation has problems with irregular patterns, so I switched columns in the second table).

I used REDUCE to scan the tables by index and generate the output selection from each input.

= IFERROR(
    TEXTJOIN(", ",, 
      SORT(
        REDUCE(Selection,{1,2,3,4}, LAMBDA(tgt,tbl, NextTargetλ(tgt, tbl)))
      )
    ),
  "")

I was surprised that it worked.  The complexity came when I needed to trap errors that occurred when there were no matches.  

NextTargetλ = LAMBDA(tgt, tblNum,
    DROP(
        UNIQUE(
            REDUCE("",tgt,
                LAMBDA(list,t,
                    LET(
                        returnlist,TblColλ(tblNum, 1),
                        lookupList,TblColλ(tblNum, 2),
                        IF(
                            AND(t<>"?",OR(lookupList=t)),
                            VSTACK(list,FILTER(returnlist, lookupList=t,"?"),
                            list)
                        )
                    )
                )
            ),
        ),
    1)
);

Now I suppose I should do the decent thing and ensure Riny's solution works with my altered data.  

[p.s.  I relinked the joins but, oddly, I finished up with a situation in which Refresh resets the selection to "Sophie"]

[#10]

@Peter Bartholomew 

Amazing to see what you came up with. Note that I didn't make the selection dynamic. In one of the queries I just hard-coded (my bad) the 7th entry from the last table, and used that to work my way back to the first table. Both the Selection and the end Results are loaded back to Excel in B19 and B20. That's why the drop down you created in B19 would  reset itself to Sonia, every time you refreshed PQI

Made the selection dynamic now (attached) and it seems that there is a mismatch between PQ and the Lambdas in the cases for Anne and Amber. I'm showing Amber in the attached file where the Lambda doesn't pick-up number 1. The red highlights are done with CF, the yellow ones are done manually :), just to illustrate this case.

[#11]

@Riny_van_Eekelen 

Thanks for that.  You are correct that my solution is missing a term.  It appears that the #CALC! error, caused by filtering using a criterion that is never met, isn't being trapped correctly.  The message for me is that I need to build the error trapping into the solution from the outset and not add it as an afterthought.  Whereas PQ handles non-existent joins gracefully by returning a null, Excel creates an error, so life gets a little harder.

Thanks for explaining why Sonia was so persistent, I did wonder whether I had introduced the fault when adjusting the joins.