9

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.

  • Are the number of rows fixed or it will be change over time? – Rubén Jul 18 '15 at 15:29
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).

  • 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 Jul 17 '15 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. – Rubén Jul 17 '15 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 Jul 20 '15 at 8:02
3

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

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

  • 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. – Rubén Jul 17 '15 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 Jul 20 '15 at 8:01

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.