Consolidation of tables

Hi,

I have three tables (1, 2, 3), each with three columns (A, B, C), and need to consolidate those into one table 4 with three columns.

Table 1
A: Data
B: Blank
C: Blank

Table 2
A: Sometimes data, if data in B
B: Data
C: Blank


Table 3
A: Blank
B: Sometimes data, if data in C
C: Data


Table 4
A: Data from A in Table 1 and Table 2, sorted in ascending order
B: Data from B in Table 2 and Table 3, sorted in ascending order
C: Data from C in Table 3, sorted in ascending order


Table 1 is really just one column of data (A), but I have added two dummy columns to show how the data fit in with the other tables.
Table 2 is actually two columns of data (A and mainly B), but I have added dummy column C for clarity.
Table 3 is actually two columns of data (B and mainly C), but I have added dummy column A for clarity.


I need a dynamic array formula which can produce the results seen in table 4 of the attached workbook.

If it is possible to have one formula for each of the columns A, B and C of table 4, that would be great. Otherwise, one formula for all three columns is also fine.

It would also be good if the data from table 1, 2 and 3 were brought into the formula as individual columns, since the columns A, B and C in these tables won't necessarily be side by side in real use, and also the dummy columns will not be present.

A key feature of the data is that a single A can point to many Bs which again can point to many Cs (a single C can point to only one B which again can point to only one A).

Regards,
Marbleking

This is really really tough question.

Please try

Formula:

=LET(a,A7:C16,b,E7:G16,c,I7:K16,aa,INDEX(a,,1),ba,INDEX(b,,1),bb,INDEX(b,,2),cb,INDEX(c,,2),cc,INDEX(c,,3),ca,IF(cc,IF(cb,cb,"C"),""),

d,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,bb,ca)&"</m></x>","//m")),f,TEXT(COUNTIFS(cb,d),"0;;1"),

bc,FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&d,f)),"/m","x",1)&"</m></x>","//m"),

ab,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,XLOOKUP(bc,bb,IF(ba,ba,"A"),"A"),REPT(aa,NOT(COUNTIFS(ba,aa))))&"</m></x>","//m"),

sb,SEQUENCE(ROWS(bc)),bs,bc&"."&MMULT((bc=TRANSPOSE(bc))*(sb>TRANSPOSE(sb)),sb^0),

sc,SEQUENCE(ROWS(c)),cs,ca&"."&MMULT((ca=TRANSPOSE(ca))*(sc>TRANSPOSE(sc)),sc^0),

cd,XLOOKUP(bs,cs,cc,""),IFERROR(--SORT(CHOOSE({1,2,3},ab,bc,cd),{1,2,3}),""))

This is the madness formula without LET. 2543 characters in length.

Formula:

=IFERROR(--SORT(CHOOSE({1,2,3},FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,XLOOKUP(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m")),TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1"))),"/m","x",1)&"</m></x>","//m"),F7:F16,IF(E7:E16,E7:E16,"A"),"A"),REPT(A7:A16,NOT(COUNTIFS(E7:E16,A7:A16))))&"</m></x>","//m"),FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m")),TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1"))),"/m","x",1)&"</m></x>","//m"),XLOOKUP(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m")),TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1"))),"/m","x",1)&"</m></x>","//m")&"."&MMULT((FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m")),TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1"))),"/m","x",1)&"</m></x>","//m")=TRANSPOSE(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m")),TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1"))),"/m","x",1)&"</m></x>","//m")))*(SEQUENCE(SUM(--TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1")))>TRANSPOSE(SEQUENCE(SUM(--TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1"))))),SEQUENCE(SUM(--TEXT(COUNTIFS(J7:J16,UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:F16,IF(K7:K16,IF(J7:J16,J7:J16,"C"),""))&"</m></x>","//m"))),"0;;1")))^0),IF(K7:K16,IF(J7:J16,J7:J16,"C"),"")&"."&MMULT((IF(K7:K16,IF(J7:J16,J7:J16,"C"),"")=TRANSPOSE(IF(K7:K16,IF(J7:J16,J7:J16,"C"),"")))*(SEQUENCE(ROWS(K7:K16))>TRANSPOSE(SEQUENCE(ROWS(K7:K16)))),SEQUENCE(ROWS(K7:K16))^0),K7:K16,"")),{1,2,3}),"")

Better use Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    T3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    T12 = Table.ExpandTableColumn(Table.NestedJoin(Table.SelectColumns(T1,"A"),"A",Table.SelectColumns(T2,{"A","B"}),"A","AA",JoinKind.FullOuter), "AA", {"A", "B"}, {"A.1", "B"}),
    AA = Table.AddColumn(T12, "AA", each if [A] = null then [A.1] else [A]),
    Replaced1 = Table.ReplaceValue(AA,null,19999,Replacer.ReplaceValue,{"B","AA"}),
    T23 = Table.ExpandTableColumn( Table.NestedJoin(Table.SelectColumns(Replaced1,{"AA","B"}),"B", Table.SelectColumns(T3,{"B","C"}),"B","CC",JoinKind.FullOuter), "CC", {"B", "C"}, {"B.1", "C"}),
    BB = Table.AddColumn(T23, "BB", each if [B]=null then [B.1] else [B]),
    SelectCol = Table.SelectColumns(BB,{"AA", "BB", "C"}),
    Replaced2 = Table.ReplaceValue(SelectCol,null,19999,Replacer.ReplaceValue,{"AA", "BB", "C"}),
    Sorted = Table.Sort(Replaced2,{{"AA", Order.Ascending}, {"BB", Order.Ascending}, {"C", Order.Ascending}}),
    Replaced3 = Table.ReplaceValue(Sorted,19999,null,Replacer.ReplaceValue,{"AA", "BB", "C"})
in
    Replaced3

New one, Shorter with better logic

with Let

Formula:

=LET(a,A7:A16,b,E7:G16,c,I7:K16,ba,INDEX(b,,1),bb,INDEX(b,,2),cb,INDEX(c,,2),cc,INDEX(c,,3),ca,IF(cc,IF(cb="","ZZ",cb),""),

bc,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,ca,REPT(bb,ISNA(MATCH(bb,cb,))))&"</m></x>","//m"),

ab,XLOOKUP(bc,bb,IF(ba="","ZZ",ba),"ZZ"),

aa,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,ab,REPT(a,ISNA(MATCH(a,ba,))))&"</m></x>","//m"),

rs,IFNA(SORT(CHOOSE({1,2,3},aa,bc,IF(cc="","",cc)),{1,2,3}),""),IF(rs="ZZ","",rs))

without Let

Formula:

=IF(IFNA(SORT(CHOOSE({1,2,3},FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,XLOOKUP(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,IF(K7:K16,IF(J7:J16="","ZZ",J7:J16),""),REPT(F7:F16,ISNA(MATCH(F7:F16,J7:J16,))))&"</m></x>","//m"),F7:F16,IF(E7:E16="","ZZ",E7:E16),"ZZ"),REPT(A7:A16,ISNA(MATCH(A7:A16,E7:E16,))))&"</m></x>","//m"),FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,IF(K7:K16,IF(J7:J16="","ZZ",J7:J16),""),REPT(F7:F16,ISNA(MATCH(F7:F16,J7:J16,))))&"</m></x>","//m"),IF(K7:K16="","",K7:K16)),{1,2,3}),"")="ZZ","",IFNA(SORT(CHOOSE({1,2,3},FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,XLOOKUP(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,IF(K7:K16,IF(J7:J16="","ZZ",J7:J16),""),REPT(F7:F16,ISNA(MATCH(F7:F16,J7:J16,))))&"</m></x>","//m"),F7:F16,IF(E7:E16="","ZZ",E7:E16),"ZZ"),REPT(A7:A16,ISNA(MATCH(A7:A16,E7:E16,))))&"</m></x>","//m"),FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,IF(K7:K16,IF(J7:J16="","ZZ",J7:J16),""),REPT(F7:F16,ISNA(MATCH(F7:F16,J7:J16,))))&"</m></x>","//m"),IF(K7:K16="","",K7:K16)),{1,2,3}),""))