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:
Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines
google spreadsheets;google forms;formulas
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)