7

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
  • 2
    Mocking up some sample data to include in your question would probably be helpful. Otherwise, there's an awful lot of guessing that needs to happen. – ale Dec 28 '15 at 17:35
  • Hi @AlE. I have added a link to a spreadsheet that mocks what I'm trying to do. – Zlatty Dec 28 '15 at 17:48
  • 2
    That's helpful, but it would still be better for the question to be completely contained in the post. The point of Stack Exchange is to be a resource for people who come along later with the same question. That you happen to get helped along the way is just a happy coincidence. – ale Dec 28 '15 at 17:58
  • Sure thing @AlE. - I have updated my original post with the data. – Zlatty Dec 28 '15 at 18:09
6

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

  • This is exactly what I'm looking for. Thank you! – Zlatty Dec 28 '15 at 18:44
  • Hey @Rubén, is it possible to filter the query? After combining the two lists, I would like to rearrange it. – Zlatty Dec 28 '15 at 18:55
  • 1
    @Zlatty: Yes, it's possible. Actually, it already include one filtering criteria: Where Col1<>''. You could add more criterias and use some other SQL operators. Have you already took a look to the QUERY() help article? – Rubén Dec 28 '15 at 18:59
  • 1
    Looking at it now. It seems that "Select * Where Col1<>'' order by Col1" does the trick. Now to figure out column situation as the data from my sources does not seem to match (a bit different than my example). Thanks again. – Zlatty Dec 28 '15 at 19:03
  • 1
    @Zlatty: I also try to help coworkers and very frequently have to deal with the same situation: once the data from different sources is put together, then arise the issues with the data collection processes. Good luck! – Rubén Dec 28 '15 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.