3

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'?

2
  • The one with "cat3"? It shows up for me.
    – user79865
    Aug 26, 2015 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. Aug 26, 2015 at 12:32

1 Answer 1

1

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)
2
  • 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. Aug 27, 2015 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. Aug 27, 2015 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.