Are you struggling to combine two tables with unequal rows horizontally in Google Sheets? You’re not alone! It can be quite a challenge, but fear not, my friend. I’m here to share some secrets with you on how to overcome this hurdle and make your Google Sheets experience a breeze.
The Old School Approach
Let’s start with the old school approach of combining two physical tables that have different numbers of rows. Here’s how you can do it:
Steps
-
First, generate 100 rows with error values. You can use the Sequence function for this.
=ArrayFormula(sequence(100,2)/0)This formula will give you a hundred rows and two columns, which match the columns in each table.
-
Now, add these 100 rows of error values below the tables. But before that, let’s make the #DIV/0 errors appear as blanks. You can do this by wrapping the Sequence formula with IFERROR. Here’s an example of the formula for Table #1:
={C1:D3;iferror(ArrayFormula(sequence(100,2)/0))}Repeat the same process for Table #2.
-
Once you’ve added the error rows, you can constrain the rows in each table to 10 using the Array_Constrain function. Here are the formulas for Table #1 and Table #2, respectively:
=array_constrain({C1:D3;iferror(ArrayFormula(sequence(100,2)/0))},10,2) =ARRAY_CONSTRAIN({F1:G4;iferror(ArrayFormula(sequence(100,2)/0))},10,2) -
Finally, you can combine these two tables horizontally by using the curly brackets to enclose both table arrays:
={ array_constrain({C1:D3;iferror(ArrayFormula(sequence(100,2)/0))},10,2), array_constrain({F1:G4;iferror(ArrayFormula(sequence(100,2)/0))},10,2) }Feel free to fine-tune the formula according to your needs.
Congratulations! You’ve successfully combined two physical tables with unequal numbers of rows horizontally in Google Sheets. Wasn’t that easy?
The New Approach
Now, let’s move on to the new approach, which is recommended and even simpler. This method is used to combine two or more tables with mismatching rows horizontally. Here’s how you can do it:
=IFERROR(HSTACK(C1:D3,F1:G4))The HSTACK function does the magic here by adding #N/A cells to match the number of rows. The IFERROR function removes those #N/A cells.
Combining Query Results
But what if you want to combine two Query formula results with unequal numbers of rows horizontally? Don’t worry, I’ve got you covered!
The Old School Approach (Two Virtual Tables)
The real purpose of this tutorial is not only to help you combine two physical tables with different numbers of rows horizontally, but also to guide you in combining two Query formula results with unequal numbers of rows.
Let’s say we have two Query formula results, G1 and J1. You can change the table references in the old school approach formula (FORMULA 1) with these two Query formulas. Here’s an example:
=ArrayFormula( {array_constrain( {query(A1:B,"Select A, sum(B) where A is not null group by A",1); iferror(sequence(100,2)/0)},10,2), array_constrain( {QUERY(D1:E,"Select D, sum(E) where D is not null group by D"); iferror(sequence(100,2)/0)},10,2)} )The New Approach (Two Virtual Tables)
Now, if you prefer a simpler solution for combining two Query formula results, you can use the following formula:
=IFERROR( HSTACK( QUERY(A1:B,"Select A, sum(B) where A is not null group by A",1), QUERY(D1:E,"Select D, sum(E) where D is not null group by D") ) )Just like that, you can easily combine two Query results with unequal numbers of rows horizontally in Google Sheets.
There you have it, my dear friend! You are now equipped with the knowledge to combine tables with unequal rows horizontally in Google Sheets. Enjoy your newfound skills and let your spreadsheets shine!
Example_Sheet_9720
For more tips and tricks on Google Sheets, visit Crawlan.com. Happy sheeting!
