Google-sheets – Can you do a UNION SELECT in a Google Sheet

google sheetsgoogle-sheets-arrayformulagoogle-sheets-arrays

I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three columns stating where the three people are located.

I need to produce a view/query that takes those three columns and puts them 'underneath' each other, and not next to each other. In a database I would have done something like the query below, I've called my 'sheet' tblMain, and stated the relevant four columns (I also need the subject ID).

tblMain:

ID        RALocation        RBLocation        RCLocation

Query:

Select ID, RALocation as Location, 'RA' as Role from tblMain
Union
Select ID, RBLocation as Location, 'RB' as Role from tblMain
Union
Select ID, RCLocation as Location, 'RC' as Role from tblMain

Does anyone know if there is a way of doing this in Google Sheets? I don't mind building more than one sheet and then combining them at the end, but I'm a bit stuck at how to do this.

Best Answer

I offer two versions, depending on whether blank cells should be kept. The basic idea is the same:

  1. Pick a character that is not contained in your table (e.g., an exotic Unicode character accessible via CHAR).
  2. Use it in the JOIN command for each of three columns.
  3. Concatenate the results, adding the character in between.
  4. SPLIT the concatenated string by the same character.
  5. TRANSPOSE the result.

For example: 

=TRANSPOSE( SPLIT( JOIN(CHAR(57344),A1:A) &CHAR(57344)& JOIN(CHAR(57344),B1:B) &CHAR(57344)& JOIN(CHAR(57344),C1:C), CHAR(57344) ) )

where for joining/splitting, I picked the character CHAR(57344), which is designated for private use and therefore should not be present in any valid input.

The above formula removes blank entries, because this is what SPLIT does. This may be convenient if your data does not have blanks: saves you the trouble of tracking where the last row of the data is. But other times, blanks should be preserved.

To preserve blanks, I follow the answer by Jacob Jan Tuinstra except that I used another private use Unicode instead of space (who knows, maybe you have some cells that contain just a space and you want to keep those). This involves two extra steps: after joining everything as before, I use SUBSTITUTE to replace CHAR(57344) by CHAR(57344)&CHAR(57345), then split as before, and replace CHAR(57345) by empty string. 

=TRANSPOSE( ARRAYFORMULA(SUBSTITUTE( SPLIT( SUBSTITUTE(...joins... , CHAR(57344),CHAR(57344)&CHAR(57345)), CHAR(57344) ), CHAR(57345), "")))

The full result looks like this (notice that now I specify where the last row of data is): 

=TRANSPOSE( ARRAYFORMULA(SUBSTITUTE( SPLIT( SUBSTITUTE(JOIN(CHAR(57344),A1:A9) &CHAR(57344)& JOIN(CHAR(57344),B1:B9) &CHAR(57344)& JOIN(CHAR(57344),C1:C9), CHAR(57344),CHAR(57344)&CHAR(57345)), CHAR(57344) ), CHAR(57345), "" )))

One more caveat: strings in Google Sheets cannot exceed 50000 characters in length. If your data is too large for string-based approach to work, use a script (this one is a good place to start).

Related Topic