Oct 29, 2019
Query is partially adding all content into one row instead of splitting out into multiple rows?
https://imgur.com/gallery/FWN0Awd Is a gallery guide of the sheets
I wanted to add in some extra details and added extra rows onto all my main sheets that I was using to pull data.
Everything seemed to be updating and feeding over alright but then I realized that it was merging a bunch of data into one row before doing the correct thing below.
Everything seemed to be updating and feeding over alright but then I realized that it was merging a bunch of data into one row before doing the correct thing below.
Not sure how to fix this?
BAD Documet: https://docs.google.com/spreadsheets/d/1NxecWST3rRGm-0UEPipXda8fHXqpqPApDDucUg4FUEk/edit?usp=sharing
Original Document without changes: https://docs.google.com/spreadsheets/d/1LSRiZqOljcPOwNI0a1x7KslUgCztVOO8SLWjDjW9RSY/edit?usp=sharing
*If you need to play around and make changes, please make a copy to your own drive!
Original Document without changes: https://docs.google.com/spreadsheets/d/1LSRiZqOljcPOwNI0a1x7KslUgCztVOO8SLWjDjW9RSY/edit?usp=sharing
*If you need to play around and make changes, please make a copy to your own drive!
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
You need to add ,0 to the query() function as I explained
=QUERY({"Data Range"} , " query string " , 0 )
You are not including a header row in the data range so you need to tell the query() function a header row is not being used.
Ben Liebrand
Original Poster Smote marked this as an answer
It is important to always use the query header option no matter what.
So instead of
=QUERY({'TEA MAIN'!A4:AF; 'TEA CART'!A4:AF; 'Adagio Sample Sets'!A4:AF},"Select * where Col2 = 'white'")
use
=QUERY({'TEA MAIN'!A4:AF; 'TEA CART'!A4:AF; 'Adagio Sample Sets'!A4:AF},"Select * where Col2 = 'white' ",0)
and instead of
=QUERY({'TEA MAIN'!A3:AN; 'TEA CART'!A3:AN; 'Adagio Sample Sets'!A3:AN},"Select * where Col2 = 'black'")
use
=QUERY({'TEA MAIN'!A3:AN; 'TEA CART'!A3:AN; 'Adagio Sample Sets'!A3:AN},"Select * where Col2 = 'black' ",0)
If you do not use the query header option then the query makes a guess and sometimes it guesses wrong.
By always using the header option it means you are in control and am not allowing the query function to make the wrong guess.
Hope this helps,
Ben Liebrand
Original Poster Smote marked this as an answer
Oct 29, 2019
The query can be unpredictable in certain situations. Why it doesn't show a #VALUE! error is not clear. You can see that as soon as you add a column in TEA MAIN the formula breaks. Even if you adjust the range back to the hypothetical A3:AB, it still throws the error. Adjust the ranges and you should be all set.
And Ben's advice is very wise, make sure to tell the query if you know the data doesn't have a header.
Cheers,
G
Original Poster Smote marked this as an answer
All Replies (7)
Oct 29, 2019
Hi Smote,
You seem to have typos in the ranges provided to the QUERY() - they reference columns that don't exist (AN, AF). You can usually easily spot this because the ranges display in black instead of the usual colors.
Hope this helps!
Cheers,
G
Oct 29, 2019
Ooops, I see what I did! However, my question still stands since my original query should still work yes?
=QUERY({'TEA MAIN'!A3:AB; 'TEA CART'!A3:AB; 'Adagio Sample Sets'!A3:AB},"Select * where Col2 = 'black'")
But as you can see here, it definitely doesn't.
But as you can see here, it definitely doesn't.
It is important to always use the query header option no matter what.
So instead of
=QUERY({'TEA MAIN'!A4:AF; 'TEA CART'!A4:AF; 'Adagio Sample Sets'!A4:AF},"Select * where Col2 = 'white'")
use
=QUERY({'TEA MAIN'!A4:AF; 'TEA CART'!A4:AF; 'Adagio Sample Sets'!A4:AF},"Select * where Col2 = 'white' ",0)
and instead of
=QUERY({'TEA MAIN'!A3:AN; 'TEA CART'!A3:AN; 'Adagio Sample Sets'!A3:AN},"Select * where Col2 = 'black'")
use
=QUERY({'TEA MAIN'!A3:AN; 'TEA CART'!A3:AN; 'Adagio Sample Sets'!A3:AN},"Select * where Col2 = 'black' ",0)
If you do not use the query header option then the query makes a guess and sometimes it guesses wrong.
By always using the header option it means you are in control and am not allowing the query function to make the wrong guess.
Hope this helps,
Ben Liebrand
Original Poster Smote marked this as an answer
You need to add ,0 to the query() function as I explained
=QUERY({"Data Range"} , " query string " , 0 )
You are not including a header row in the data range so you need to tell the query() function a header row is not being used.
Ben Liebrand
Original Poster Smote marked this as an answer
The query can be unpredictable in certain situations. Why it doesn't show a #VALUE! error is not clear. You can see that as soon as you add a column in TEA MAIN the formula breaks. Even if you adjust the range back to the hypothetical A3:AB, it still throws the error. Adjust the ranges and you should be all set.
And Ben's advice is very wise, make sure to tell the query if you know the data doesn't have a header.
Cheers,
G
Original Poster Smote marked this as an answer
Oct 29, 2019
I had the 0 in my very first original version but then when I was moving things around and started copying sheets it must have gotten removed - and then of course it worked for a bit before breaking!
Thanks a million! I knew it was something silly and just couldn't figure out what!
Hi, Smote,
You are welcome and thank you for your feedback.
I'm glad I was able to help.
The same applies to the White Tea tab. You also need to include the header option so the output displays correctly.
Ben Liebrand