Combining sheets with different number of colums and rows

Question

How can I combine two sheets with different number of rows and columns in Excel, Libre Office or Google Sheets? Example:

Sheet 1:

Name	Age	Phone
Mike	35	197 158 348
Dave	38	394 953 429

Sheet 2:

Name	Email	Birthday
Mike	[email protected]	2/5/88
Dave	[email protected]	3/7/85
John	[email protected]	4/6/94

Result:

Name	Age	Phone	Email	Birthday
Mike	35	197 158 348	[email protected]	2/5/88
Dave	38	394 953 429	[email protected]	3/7/85
John			[email protected]	4/6/94

Answer 1

Google Sheets

This solution applies to Google Sheets and supports different sized ranges, height and/or width, as long as the first column in each range is a common index/ID, such as the shared Names column in your example. You can add and remove ranges easily.

Formula

=LET(
   Σ,LAMBDA(x, QUERY(x,"WHERE Col1<>''")),
   a,Σ(Sheet1!A1:C), b,Σ(Sheet2!A1:C),
   Δ,LAMBDA(x,y, INDEX(VLOOKUP(x,y, 
      SEQUENCE(1,COLUMNS(y)-1,2),0))),
   BYROW(UNIQUE(INDEX({a;b},,1)), LAMBDA(r, 
      IFNA({r, Δ(r,a), Δ(r,b)}))))

Sorting

If sorting is desired it can be added by wrapping the last formula in QUERY which supports ordering by any column without disturbing the headings. In the following example the results are sorted by Name:

=LET(
   Σ,LAMBDA(x, QUERY(x,"WHERE Col1<>''")),
   a,Σ(Sheet1!A1:C), b,Σ(Sheet1!A1:C),
   Δ,LAMBDA(x,y, INDEX(VLOOKUP(x,y, 
       SEQUENCE(1,COLUMNS(y)-1,2),0))),
   QUERY(BYROW(UNIQUE(INDEX({a;b},,1)), LAMBDA(r, 
       IFNA({r, Δ(r,a), Δ(r,b)}))),
       "ORDER BY Col1 ASC",1))

Explanation

1. LET allows storing values and calculations.
2. rangeA and rangeB are your two data ranges including headings. They can be different dimensions but the first column must be the index/ID column (Name in your example).
3. Σ stores a LAMBDA function to filter blank rows out of the ranges. This is simply done to reuse the same formula more than once. It accepts one argument x which can be a range (or array).
4. a and b respectively store the results of applying the function Σ to one of the ranges to remove blank rows.
5. Δ is a second LAMBDA function it accepts two arguments x and y, where x is a single Name and y is an array where the first column contains Names. A LAMBDA is used again to reuse the code for the two arrays a and b rather than duplicating it.
   1. The VLOOKUP is used to locate the row containing x in the array y. It is placed within an array function INDEX so that multiple values can be returned from the same row with a single formula.
   2. SEQUENCE is used to return an array of columns for VLOOKUP's index argument. The array is 1 row x Columns(y)-1 columns (because we don't need the Names column) and the numbering starts at 2 (second column, skipping Names).
6. LET's final argument is the formula whose result is actually returned. BYROW passes each unique value from the Names column of the two arrays, one by one into a LAMBDA that stores the current Name in n. INDEX is used to grab the first column.
7. The LAMBDA's formula, for each n passed in, returns an array made by combining n in the first column with the array values (columns) returned by Δ for each of a and b.
8. Version with Sort
   The second version incorporates a sort using QUERY in order to leave the headings undisturbed.