Google Sheets – Combine Two Worksheets and Preserve Updates

google sheets

I am trying to help a co-worker out with semi-automating a task. I need to combine two worksheets data into one worksheet. Let's call these A, B and C for the combined one. Data is copied pasted from two different sites into A and B. The data is in a similar format but only two fields need to be in worksheet C.

What I want to do is for my co worker to paste the data into worksheets A and B from their sources, and then for the two fields to be transitioned over to worksheet C.

Both A and B's data vary in number of rows. Right now the data is pulled by a simple reference (=worksheetA!B2) for each field and has to be manually compared as the rows vary each time the data needs to be combined.

Is there any way this can simply be done though some formula, or will I have to create a script?

Edit 1:
Per request, here is an example spreadsheet.

Edit 2:
Per request, here is some sample data:

Worksheet A

Something   else    something different
a           37      58.2
asdf        4345    345.3
sdflv       234     233.2

Worksheet B

Something   else 
ASDFASD     43
EE#         5646 
VVBG        234
$#GG        4344

Worksheet C 

Something    else 
a            37
asdf         4345
sdflv        234
ASDFASD      43
EE#          5646
VVBG         234
$#GG         4344

Best Answer

Short answer

Use arrays referencing the column headers and the all the rows from the desired columns and filter them:

=QUERY({A!A1:B1;A!A2:B;B!A2:B},"Select * Where Col1<>''",1)

Explanation

Google Sheets allow to specify arrays by enclosing the values between braces { , }. If your spreadsheet use point . as the decimal separator, use comma , to separate columns and semicolon ; to separate rows, other way use a semicolon to separate columns and a backslash \ as row separator.

In the proposed formula the array has three sets of values

  • A!A1:B1 is used to get the column headers
  • A!A2:B, B!A2:B are used to get all the cells in the columns A and B of the worksheets A & B from the second row to the last row.
  • The QUERY() function is used to filter the values to avoid the inclusion of the blank cells in the final result.

References

Related Topic