2

I need to join columns with a formula (not a script) in Google Sheets in a different way than others I've found--here is my original data, supposing that the Avengers train by playing racquetball:

Full Name   North Court   Middle Court   South Court   Reserved Court 
---------   -----------   ------------   -----------   --------------
Ironman     Tues 4:00PM                                North Court
Hulk                      Tues 5:00PM                  Middle Court
Thor                                     Tues 5:00PM   South Court
Hawkeye                   Tues 6:00PM                  Middle Court
Antman                                   Tues 4:00PM   South Court
Spiderman                 Wed  5:00PM                  Middle Court
Captain Am  Wed  5:00PM                                North Court

I need to combine this data horizontally as follows:

Full Name   Reserved Time   Reserved Court 
---------   -------------   --------------
Ironman     Tues 4:00PM     North Court
Hulk        Tues 5:00PM     Middle Court
Thor        Tues 5:00PM     South Court
Hawkeye     Tues 6:00PM     Middle Court
Antman      Tues 4:00PM     South Court
Spiderman   Wed  5:00PM     Middle Court
Captain Am  Wed  5:00PM     North Court

I can achieve this on one row with the following formula:

=split(join(";",'Form Responses'!C2:G2),";")

However, I can't figure out how to make this work with an arrayformula or filter. I can't use a copydown--I need one formula/function in one cell like an arrayformula to display data in columns A through E on Sheet1 into columns A through C on Sheet2.

1

Assuming the columns you have are C-G, entering

=arrayformula({C2:C, D2:D & E2:E & F2:F, G2:G})

in the cell H2 will have the desired effect. The arrayformula indicates that in every row, one should have three entries: one from C, one is concatenation of D,E,F, and the last from G.

  • Ah, thank you, @sandwich! I knew it shouldn't be as hard as I was trying to make it--nice solution! – Carl Walker May 23 '16 at 13:21
  • Question @sandwich: the # of submissions will vary, so I can't put C2:C9, for example. What is the best method of getting only non-blank cells? I thought about using an if(not(isblank(...))) around each, but that is cumbersome and I'm guessing I'm making that harder than it should be as well...plus, I'm not even sure that would work. – Carl Walker May 23 '16 at 13:37
  • Replace C2:C with filter(C2:C, len(C2:C)) to keep only the rows with nonblank entries. For consistency, the other columns must be filtered by the same criterion: filter(D2:D, len(C2:C)) etc. – user79865 May 23 '16 at 13:43
  • Never mind, @sandwich -- I wrapped your arrayformula in a filter as the range and for the condition, I included my timestamp in column A in this statement: datevalue(A2:A)=datevalue(now()). So, now it just pulls the current day's reservations and nothing more. Thanks again! – Carl Walker May 23 '16 at 14:54
  • Sorry, I should have refreshed my page before posting--thanks for the len solution, that will come in handy too *added to mental toolbox – Carl Walker May 23 '16 at 14:56

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.