Google-sheets – How to combine and consolidate rows from multiple sheets into one and sort them

google sheetssorting

I've few sheets where rows are populated dynamically via IMPORTXML and ImportJSON with the same columns, but they can't be sorted.

So I'd like to generate new sheet which will populate rows from other sheets, so consolidated rows can be sortable. Copying won't help when feed will change as main sheet won't be up-to-date.

Example:

I'd like to consolidate these 3 YouTube Data feeds from different countries:

=ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/GB/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content/src", "noInherit,noTruncate,rawHeaders")
=ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/US/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content/src", "noInherit,noTruncate,rawHeaders")
=ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/AT/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content/src", "noInherit,noTruncate,rawHeaders")

into one sheet and sort them by title.

Best Answer

Short Answer

Use arrays in Google Sheets and SORT() function.

Example

To combine the non continuous ranges A1:D5, H1:K100, P1:S15, the following formula will return a 4 X 120 array sorted by the first column in ascending order.

=SORT({A1:D5;H1:K100;P1:S15},1,TRUE)

The first parameter also could be any function that returns an array like IMPORTFEED.

Remarks

The URLs provided in example are not working because the YouTube Data API (v2) has been officially deprecated as of March 4, 2014.
IMPORTJSON() was retired of the new Google Sheets.

References

Related Solutions

Google-sheets – Combine two charts in one and auto-update them

You can use embedded arrays to join the two data sets for the purpose of plotting. For example, if your data is in columns A and B of two sheets, and has a header row, the following will join and sort the two sets:

={Sheet1!A1:B1; sort({filter(Sheet1!A2:B, len(Sheet1!A2:A)); filter(Sheet2!A2:B, len(Sheet2!A2:A))}, 1, true)}

Explanation:

Sheet1!A1:B1 grabs the header row. The semicolon after it works as row separator in embedded arrays.
filter(Sheet1!A2:B, len(Sheet1!A2:A)) takes all A-B cells from Sheet1 with nonempty A entry (i.e., filters out blank rows)
filter(Sheet2!A2:B, len(Sheet2!A2:A)) does the same for Sheet2. Separated by ; these get stacked one over the other.
sort({...}, 1, true) sorts by the first column, ascending.

You can also combine non-consecutive columns in this way, like E and J. The embedded array would have the structure

{Sheet1!E,  Sheet1!J; Sheet2!E, Sheet2!J}

where commas separate columns and semicolon separates rows. Specifically.

={filter(Sheet1!E2:E, len(Sheet1!E2:E)), filter(Sheet1!J2:J, len(Sheet1!E2:E)); filter(Sheet2!E2:E, len(Sheet2!E2:E)), filter(Sheet2!J2:J, len(Sheet2!E2:E))}

(and then apply sort, as before).

Note that both columns must be filtered by the same criterion (whatever it is) to make sure their contents align.

Google-sheets – How to sort groups of rows in Google Sheets

Short answer

The desired result could be obtained without scripting and this is recommended for cases where number of cells is small, especially if the OP don't know about scripting. To do so,

Add auxiliary columns for each sorting criteria
Then use the SORT() or QUERY() functions.

Extended answer

Google Sheets has some additional functions regarding those available in other spreadsheet applications. Two of them are SORT() and QUERY() which allow to get the desired result without scripting.

As QUERY() return a blank cell for those containing ? as value, the SORT() function will be used.

Original data

Assume that the original data is in A2:D13.

Auxiliary columns

Auxiliary columns will be used for sorting. Only one aggregate function is presented in order to keep the answer short.

Add the following formulas and fill down until the last row of the source data.

D2: =Row(C2)-Row($C$2)+1
E2: =ISERROR(LOOKUP("?",OFFSET($A$2,MATCH(A2,$A$2:$A$13,0)-1,2,COUNTIF($A$2:$A$13,A2))))
F2: =AVERAGEIF($A$2:$A$13,A2,$C$2:$C$13)

If you find helpful having column headers, add them to the row 1.

Key Formula

G2: =ARRAY_CONSTRAIN(SORT(A2:F13,5,TRUE(),6,TRUE(),1,TRUE()),12,3)

ARRAY_CONSTRAIN is included to limit the result to the columns of the source data.