Google Sheets - have 2 columns each with 10 rows of data - how can i combine each cells into new column?

Question

I have 2 columns with 10 rows of data (in a Google Sheet)

      A               B          C   
1  DATASET 1    DATASET 2
2  "trial 1"    "mouse 1"
3  "trial 2"    "mouse 2"
4  "trial 3"    "mouse 3"
5  "trial 4"    "mouse 4"
6  "trial 5"    "mouse 5"
7  "trial 6"    "mouse 6"
8  "trial 7"    "mouse 7"
9  "trial 8"    "mouse 8"
10 "trial 9"    "mouse 9"
11 "trial 10"   "mouse 10"

would like to combine results and have them in a separate column (column C):

      A               B          C   
1  DATASET 1    DATASET 2      
2  "trial 1"    "mouse 1"   "trial 1 mouse 1"
3  "trial 2"    "mouse 2"   "trial 1 mouse 2"
4  "trial 3"    "mouse 3"   "trial 1 mouse 3"
5  "trial 4"    "mouse 4"   "trial 1 mouse 4"
6  "trial 5"    "mouse 5"   "trial 1 mouse 5"
7  "trial 6"    "mouse 6"   .................
8  "trial 7"    "mouse 7"   .................
9  "trial 8"    "mouse 8"   .................
10 "trial 9"    "mouse 9"   .................
11 "trial 10"   "mouse 10"  .................
12                          "trial 2 mouse 1" 
13                          "trial 2 mouse 2" 
14                          .......etc.......

here is the google sheet with sample data: https://docs.google.com/spreadsheets/d/1XToxuw_86wxZoJuoxF-D5OYrjsXnReAqGBRtljqQ69k/edit?usp=sharing

(as you can see in the Gsheet) i kinda managed to combine each vale in column A with all values in column B using the following formula:

=ARRAYFORMULA(A2&" "&B2:B11)

but problems are:

1. if i want to combine the next set of data (a3 + all values in column B) i need to repeat the formula..... (a4 + all values in column B) repeat formula again.... etc...
2. if i have to repeat the same formula over and over, if i add a new set of data (column A same 10 trials but column B add 5 more mice) i need to change all formulas ....=ARRAYFORMULA(A..&" "&B2:B16)
3. don't know how to remove internal " adding a space (was just able to add a space)

Is there a way to use 1 formula only (at the top of column C) or can you please suggest me a different way to achieve my goals?

Thanks in advance

Answer 1

For problems 1 and 2, you can combine the ranges into a 2-dimensional array by transposing one of them:

ArrayFormula(A2:A & TRANSPOSE(B2:B))

Because this would create a huge matrix full of empty values, we should reduce the number of rows before combining all those cells, for example with ARRAY_CONSTRAIN.

ArrayFormula(ARRAY_CONSTRAIN(A2:A & TRANSPOSE(B2:B),COUNTA(A2:A),COUNTA(B2:B)))

(in fact, it is faster to apply the constrain to each column before merging, but you get the idea)

The result is a table like this:

"trial 1""mouse 1"   |  "trial 1""mouse 2"  |  ...
"trial 2""mouse 1"   |  "trial 2""mouse 2"  |  ...
 ...                    ...

This can be converted to a single column using FLATTEN()

Now, for problem 3, we can simply replace those double quotes with a space using REGEXREPLACE() or SUBSTITUTE().

End result:

=ArrayFormula(SUBSTITUTE(FLATTEN(
    Array_constrain(A2:A,COUNTA(A2:A),1) & 
    TRANSPOSE(Array_constrain(A2:B,COUNTA(B2:B),1))
 ),""""""," "))

Answer 2

@Ricardo Aranguren.... thanks a lot for your help.

the solution you shared worked flawlessly!!!!!

(hope you don't mind) need an extra tip from you...

How would you add, to the formula you suggested, a conditional to combine cells if both cells (you want to combine) are checked (= are TRUE)?

       A        B         C          D                           
   1          DATASET 1         DATASET 2
   2  TRUE    "trial 1"  TRUE   "mouse 1"
   3  FALSE   "trial 2"  FALSE  "mouse 2"
   4  FALSE   "trial 3"  TRUE   "mouse 3"

In column A and column C, Google Sheet, let's say i decide to add a checkbox for each value (Google Sheet => Insert => checkbox). When i check the box value becomes TRUE.

So, how can i add, to your function, a conditional that says "combine ONLY those CELLS (column B and column D) that are both TRUE (column A and column C)?

the resulting table, given the conditional, should look like this below:

       A        B         C          D         E                         
   1          DATASET 1         DATASET 2    COMBINATIONS
   2  TRUE    "trial 1"  TRUE   "mouse 1"    "trial 1 mouse 1"
   3  FALSE   "trial 2"  FALSE  "mouse 2"    "trial 1 mouse 3"
   4  FALSE   "trial 3"  TRUE   "mouse 3"

in the google sheet, line 35, you can see the checkboxes i mean:

https://docs.google.com/spreadsheets/d/1XToxuw_86wxZoJuoxF-D5OYrjsXnReAqGBRtljqQ69k/edit?usp=sharing