2

I have a Google Sheet which receives information from a google form.

I would like to collate some of this data in a separate sheet (within the same document). I would like the new sheet to combine data from 4 columns into 2 Columns since the columns contain the same kind of data which we'd like see as a list. I'd also like the blank column to be removed.

The idea being that any time a new entry is made into these fields from the google form- the list would be updated in the second sheet.

I've done an example with pictures below- so columns labelled New song set 1 and New song set 2 would be combined into a list of all new songs- with their corresponding 'first lines' next to them. The blanks would also be removed (as seen in the second picture). The order of the data isn't important.

Do you know how I can do this with formalas?

Example:

Form Data: CURRENT FORM DATA SHEET example

SECOND SHEET SORTED DATA:

new sheet sorted example

2

Since the order of data is not important, the following will do:

={filter(A1:B, len(A1:A)); filter(C1:D, len(C1:C))}

Explanation:

  • filter(A1:B, len(A1:A)) gets all A-B pairs with nonempty A entry
  • the second filter does the same for C-D
  • ={ ... ; ... } is concatenation of two arrays, putting one below the other. (See using arrays in Google Sheets.)

You can also sort the results by some field, e.g., by the first one, song name:

=sort({filter(A1:B, len(A1:A)); filter(C1:D, len(C1:C))}, 1, true)
| improve this answer | |

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.