14

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.

1

3 Answers 3

3

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).

3
  • That works a treat, and I can combine them to get the right result. Only thing I'm now trying to do is to get it to respect NULL values and pull them over, it turns out that your FILTER wasn't needed, it will take them out if it finds a NULL values. Any idea how I get it to respect the NULL?
    – AnneB
    Commented Jul 17, 2015 at 14:43
  • 1
    Actually, the "other answer" is a partial answer at this time. Besides other alternatives, the relevant missing part is about how to use embedded arrays instead of the UNION-SELECT approach. Commented Jul 17, 2015 at 19:13
  • 2
    Wonderful. I'll probably have to look at the script for when our pilot is over but for now I've got enough to go on. People here are really helpful, thank you all!
    – AnneB
    Commented Jul 20, 2015 at 8:02
8

It is possible to make a union in Google Spreadsheet very easily nowadays with this formula:

={'Sheet1'!A2:A;'Sheet2'!A2:A;'Sheet3'!A2:A}

See more info in Google Docs Help: Using arrays in Google Sheets

2
  • When I dropped the final row number as in this example it only returned the first set. Adding a final row number fixed this for me. Commented Feb 27, 2021 at 3:34
  • 1
    Not sure how it behaved back in 2016, but if I try this today with multiple columns (A1:Z) then it seems to select the first 1000 rows from sheet 1 (implicitly A1:Z1000?), places them in the merge sheet, then gets the first 1000 rows from sheet 2 and places them below the sheet 1 rows without removing empty rows (so starting at row 1001). Instead, I went with ={QUERY('Sheet1'!A1:Z,"select * where A<>''");QUERY('Sheet2'!A1:Z,"select * where A<>''")} that checks whether the first column A is non-empty to avoid blank rows in a crude way. Either way, formatting is lost unfortunately.
    – CodeManX
    Commented Apr 6, 2021 at 11:47
4

Short Answer

Google Sheets is a very powerful tool that have several alternatives to achieve a result similar to UNION-SELECT.

Alternatives

Embedded arrays

An alternative to use SPLIT-TRANSPOSE-JOIN-CHAR way is to use embedded arrays. One advantage of this method is that it doesn't require to construct/deconstruct strings, so the limit of the number of characters it's not relevant.

Emdedded array

Filtering

The above could be used in combination with FILTER() for those cases that require filtering to have a similar behavior of UNION-SELECT. Example

={Filter(A1:B2, B1:B2=2);Filter(D1:E2, E1:E2=6);Filter(G1:H2, H1:H2=12)}

Also could be used in combination with QUERY() but its results includes a headers line. To learn about how to get rid of the headers see Get rid of row in result of Google Spreadsheets query.

How to create a column of values

Use TRANSPOSE-SPLIT-REPT to create a column of values.

={Transpose(Split(REPT("RA,",6),","))}

Also QUERY() could be used to create a column of values, but removing the headers row could make the formula more complex. Use it only if the string length limitations cause problems.

Solution using Embedded Arrays and TRANSPOSE-SPLIT-REPT

Assume that the range tblMain is A1:D7, where A1:D1 is the table headers, and the data is in A2:D7.

={{"ID";A2:A7;A2:A7;A2:A7},
{"Location";B2:B7;C2:C7;D2:D7},{"Role";
Transpose(Split(REPT("RA,",6),","));
Transpose(Split(REPT("RB,",6),","));
Transpose(Split(REPT("RC,",6),","))}}

References

Appending google spreadsheet arrays - StackOverflow

2
  • You have to read more answers from me ;). I didn't find yet a official help articles about this. I know that there threads in the Google Products Forums and over the Internet, like the reference that I already included. Commented Jul 17, 2015 at 18:59
  • Thank you for this, I'm currently looking at the last solution, I think it will do very nicely for our pilot!
    – AnneB
    Commented Jul 20, 2015 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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