Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines

Question

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:

SECOND SHEET SORTED DATA:

Answer 1

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)