I have a spreadsheet with some test data: https://docs.google.com/spreadsheets/d/1Rd8uJp2KTl2ds-s_SWvUSkxNjEscAEiwAO71zSJ2A8Q/edit?usp=sharing.

In the 'All Transactions' sheet, why is row 3 from the 'source data 2' sheet included when I sort by 'category' but not when I sort by 'date'?

  • The one with "cat3"? It shows up for me. – user79865 Aug 26 '15 at 4:32
  • It now shows up because I sorted by 'category'. I'll rephrase the question, as I still don't understand why this is happening. – Chris Morris Aug 26 '15 at 12:32

The data is there, but it's out of sight because of the blank rows that are included due to the use of whole columns (A2:B). Filter the rows where A or B cells are not empty. One way to do that is by using QUERY(), i.e.:

=QUERY({{'source data 1'!A2:B};{'source data 2'!A2:B}},
"Select Col1,Col2 Where (Col1<>'' OR Col2<>'') Order by Col1",0)
  • Should it be an AND and not an OR? This assumes that I don't want to include rows that have blanks in either of those columns. – Chris Morris Aug 27 '15 at 10:20
  • It´s up to you. In the example formula, using (Col1<>'' AND Col2<>'') will exclude those rows where one of the columns has data but the other not, so row 3 and row 5 of source data 2 will not be include because of the filtering criteria. – Rubén Aug 27 '15 at 11:06

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.