Combine Two Tables with Unequal Rows Horizontally in Google Sheets

GiGiKing·Tháng ba 28, 2024·0 comments

Using the HSTACK function, you can easily combine two tables or two Query formula results with unequal rows horizontally in Google Sheets.

I’ll explain this in simple terms. Imagine you have two tables. The first table has three rows (C1:D3), while the second table has four rows (F1:G4).

Now, how can you combine these two tables with unequal numbers of rows horizontally in Google Sheets? Let me show you how.

Combine Two Tables Having Different Numbers of Rows Horizontally

Below, you’ll find two types of formulas to combine the tables, and feel free to skip the old-school approach.

Old School Approach (Two Physical Tables)

To combine the two physical tables (C1:D3 and F1:G4) horizontally in Google Sheets, follow these steps:

  1. First, generate 100 rows with error values. You can use the Sequence function for this:

    =ArrayFormula(sequence(100,2)/0)

    Here, “100” represents the number of rows, and “2” represents the number of columns in each table.

  2. After generating the error values, add these 100 rows below the tables. But before that, make sure to replace the #DIV/0 errors with blanks. You can achieve this by wrapping the Sequence formula with IFERROR.

  3. Now, constrain the rows in each table to 10 using Array_Constrain. Here are the formulas for each table:

    
Table #1:
=array_constrain({C1:D3;iferror(ArrayFormula(sequence(100,2)/0))},10,2)

Table #2:
=ARRAY_CONSTRAIN({F1:G4;iferror(ArrayFormula(sequence(100,2)/0))},10,2)

By applying the Array_Constrain function, you will limit both tables to 10 rows.

4. Finally, you can combine these two tables horizontally by using the HSTACK function. This is the updated formula:

=IFERROR( HSTACK( array_constrain({C1:D3;iferror(ArrayFormula(sequence(100,2)/0))},10,2), array_constrain({F1:G4;iferror(ArrayFormula(sequence(100,2)/0))},10,2) ) )


### New Approach (Two Physical Tables): Recommended ✔

For an easier and more efficient method, you can use the HSTACK function to combine two or more tables that have mismatching rows. In your case, you can use the following formula:

=IFERROR(HSTACK(C1:D3,F1:G4))

The HSTACK function adds #N/A cells to match the number of rows, and the IFERROR function removes them.

## Combine Two Query Results with Unequal Numbers of Rows Horizontally

Now, let's move on to combining two Query formula results with unequal numbers of rows horizontally.

### Old School Approach (Two Virtual Tables)

The purpose of this tutorial is not only to help you combine two physical tables with different rows horizontally but also to assist you in combining two Query formula results. 

Suppose you have two Query formulas and want to change the table references (C1:D3 and F1:G4) in the previous formula (FORMULA 1) with the Query formulas. Here are the steps:

1. Replace C1:D3 in FORMULA 1 with the G1 formula, and replace F1:G4 with the J1 formula. The resulting formula will be:

=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)} )


### New Approach (Two Virtual Tables): Recommended ✔

This approach is straightforward. Simply enter the two Query formulas within the HSTACK function, separated by commas, and wrap everything with IFERROR. Here's the 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”) ) )



And that's it! You can now easily combine two tables or Query formula results with unequal rows horizontally in Google Sheets. 

For more helpful tips and insights, visit [Crawlan.com](https://crawlan.com). Happy spreadsheet combining!

Example_Sheet_9720

GiGiKing

Related posts