Combine two sheets in Google sheets

Question

I'm trying to combine two sheets in a Google Sheet.

The first sheet contains a simple list of names similar to this:

+--------+  
|  Name  |  
+--------+  
| Bob    |  
| Vivian |  
| Fred   |  
+--------+

The second sheet contains a list of tasks similar to this:

+------------+---------+  
|    Task    | Task ID |  
+------------+---------+  
| Do Dishes  |       1 |  
| Vacuum     |       2 |  
| Do laundry |       3 |  
+------------+---------+

I want to combine these so that I get a sheet with those tasks for each name in separate rows:

+--------+------------+---------+  
|  Name  |    Task    | Task ID |  
+--------+------------+---------+  
| Bob    | Do Dishes  |       1 |  
| Bob    | Vacuum     |       2 |  
| Bob    | Do laundry |       3 |  
| Vivian | Do Dishes  |       1 |  
| Vivian | Vacuum     |       2 |  
| Vivian | Do laundry |       3 |  
| Fred   | Do Dishes  |       1 |  
| Fred   | Vacuum     |       2 |  
| Fred   | Do laundry |       3 |  
+--------+------------+---------+

The real data sets are much bigger, so I need a formula to do this dynamically. I've tried to tinker with QUERY and VLOOKUP, but I'm really not getting anywhere.

Below is a shared sheet with the same dummy data as above. Any help would be much appreciated!

https://docs.google.com/spreadsheets/d/1yosNoGj-PKf2Yua7-3mN3oCxIklr3IaBDojUgLunjWg/edit?usp=sharing

Jeremy Kahan Jeremy Kahan · Accepted Answer · 2018-07-26T17:07:56

Here is one way, using a little math. On the what you want sheet, put your headers in row 1 (if you want to get fancy, you could make them formulas = to the titles from the other sheets so you stay consistent if you rename). I am assuming names are in Column A. In A2 of the what you want sheet, put, =index(Names!A:A,2+QUOTIENT(row()-2,3)), and then drag it down. That will do each Name 3 times. [if you end up with more columns on the task sheet, increase the 3, or make it a function]. Now in B2 place =index(Tasks!A:A,2+mod(1+row(),3)) and drag that right into column C and down (for both columns) as far as needed. That will cycle through the entries for each user. I like how clear the question is.

EDIT: Here are the same formulas adapted to handle varying numbers of tasks.

=index(Names!A:A,2+QUOTIENT(row()-2,countA(Tasks!A:A)-1))

and

=index(Tasks!A:A,2+mod(countA(Tasks!A:A)-3+row(),countA(Tasks!A:A)-1))

If you want it to be a smidge more tranparent you could use countA(Tasks!A$2:A) in place of countA(Tasks!A:A)-1), and change the -3 to a -2 (where 2 is the row where the data start and the -1 is gone because the header "Task" does not count as a task).

Combine two sheets in Google sheets

1 Answers