CODEWITHSUNDEEP
excel
William
William

Reputation: 373

Excel - Combine data from multiple tables dynamically

I would like to combine three different tables in Excel. I am struggling with the fact that the tables can vary in length. For example:

table 1 table 2 table 3

What I would like to achieve is all the tables' data in one table without empty spaces. So first the two entries from the first table then the three entries from the second table and lastly the entry from the third table. But the amount of rows in each table can vary.

output table

How can I do this dynamically so when the amount of entries in the tables change it can handle this? I'm using Mac with Office365. Thanks!

EDIT: Output with Ron Rosenfeld's solution, the range of the list goes down from cell 5 - cell 103. Could this be reduced to 5 - 15?: intermediate solution

Upvotes: 0

Views: 2815

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60484

If you have Excel 2019 or Office 365, with the FILTERXML and TEXTJOIN functions, you can use:

 =FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,Table1,Table2, Table3) & "</s></t>","//s[.!=0]")

If those zero's are really blanks, you can omit [.!=0] from the xPath argument, but it won't hurt to leave it there

enter image description here

Edit:

With MAC versions of Office 365 that do not have the FILTERXML function, I believe the following will work:

=LET(
a,299,
x,IF(SEQUENCE(99,,0)=0,1,SEQUENCE(99,,0)*a),
y,TEXTJOIN(REPT(" ",a),TRUE,Table19,Table20,Table21),
z, TRIM(MID(y,x,a)),FILTER(z,(z<>"0")*(z<>""))
)

Note the a parameter in the above function

  • Because of how the splitting algorithm works, the sequence for each cell will not always start at the beginning of a string.
    • Hence, if there are enough letters in the various strings, the start number may eventually get offset enough to cause a split in the wrong location
  • One fix is to use an arbitrarily large number of space's to insert.
  • 99 is frequently large enough, but not for this data set.
  • 299 seems to be large enough for the data set as shown in your actual data.
    • I believe the minimum number should be the sum of the lengths of all the characters in the original tables (including the 0's) plus one (1). But not sure of this.
    • You can certainly adjust it as needed
  • If the number becomes too large, you could run into the 32,767 character limitation. If that happened, an error message would occur.

So, if you wanted to compute a, dynamically, you could try something like:

=LET(
a,SUM(LEN(Table19[Column1]),LEN(Table20[Column1]),LEN(Table21[Column1]))+1,
x,IF(SEQUENCE(99,,0)=0,1,SEQUENCE(99,,0)*a),
y,TEXTJOIN(REPT(" ",a),TRUE,Table19,Table20,Table21),
z, TRIM(MID(y,x,a)),FILTER(z,(z<>"0")*(z<>""))
)

but no guarantees.

Upvotes: 1

p._phidot_
p._phidot_

Reputation: 1952

Assuming the data is in A:C, and empty cell is blank (not 0).

In E1 put :

=IF(ROW()>COUNTA(A:C),"",
    INDEX(A:C,
        IF(ROW()<=COUNTA(A:A),ROW(),IF(ROW()<=COUNTA(A:B),ROW()-COUNTA(A:A),ROW()-COUNTA(A:B))),
        IF(ROW()<=COUNTA(A:A),1,IF(ROW()<=COUNTA(A:B),2,3)))    
   )

Idea : use row() to guide in selection in index. counta() is used guide converting 'row()' to usable index numbers. Also make the output cell blank "" for row() > counta(a:c).

Please share if it works/not.

Upvotes: 0

Related Questions