It gets a little slow but sure makes it flexible. If I'm not mistaken one could even add new columns to the original tables and it would keep working as long as the new columns get added in between other columns. Otherwise the referenced ranges do not update.
I am a complete amateur because I don't understand why the 'key' is being shown three times . I guess I should be watching many more of your awesome videos. thank you sir!
Great video. I've avoided arrayformula vlookups with multiple column returns in the past because of performance issues. I think you've even warned against doing it. Anyways, I've opted to just drag down formulas preemptively with the if blank then blank escape to hopefully save on calculations.
Personnaly, I use IFNA to remove NA from the table and the QUERY with "SELECT * WHERE Col1 IS NOT NULL" to remove empty rows and prevent from adding new empty rows in the sheet which is improving the performance.
As someone who uses SQL databases and data frames I find it fascinating how difficult a left join is in Google sheets. The creator did a great job figuring that out 😅
Awesome, lots of contents in one single video. I have a doubt: I'm often using IMPORTRANGE function and every now and then I've got to bring within IMPORTRANGE a column with a STRING as a constant, for instance the name of the sheet I am importing it from. How do I manage to do that? Creating a custom array with { } or using QUERY? Thanks in advance!
Loving your videos, very helpful. I have a question that may not be directly related to this video, but I will ask it here anyway, hope you understand. I created 37 sheets with historic price data, instead of typing out the sheet name (other) and the cell on the (current) sheet where I want info onto, I created a list of the sheet names in the first column and attempted to use the cell reference to point to the worksheet. What I get is an error code or N/A or unresolved sheet name. =(A2)!B14 where A2 refers to the cell on current page where the other sheet name is listed, and !B14 is the cell on the other sheet I want the info from, as example. Attempted with various quotes etc. Hope you can help!
@@ExcelGoogleSheets thanks for getting back to me - I found the video you did on indirect function and it solved my issue. Seriously looked high and low...
Very helpful! Thank you very much! I am wondering if there is any way to filter a range using =Filter() formula and the condition is that one column should not contain a list of values; something like =Filter(Sheet1!A:Z , Sheet1!A:ASheet3!$A$1:$A$20). Thank you in advance.
Could you please make a video showing how to use regular expressions within the find and replace function of Google sheets. I often find that this might be useful in correcting formulae that I have written that are not using arrayformulas or lookup tables and therefore I need to change each formula by hand
Sir, I have a google workbook which has sheets with different names, I want to create a single sheet, in which the data of all the sheets is merged. But only the selected number of coloum from all the sheets are required, which should be connected to the drop down list on the main sheet. what is the full query
Wondering why you key by "Owners!D2:D" in the formula and not just by " 'Joined Tables'!D2:D' "? otherwise, great video! Was wondering how to achieve a dynamic row count table join with multiple columns (previously used index match) also wondering if there's any implementation with the new XLOOKUP? I was only able to achieve single row multi column or single column multi row joins so wondering if you had a solution for this.
Sorry, don't really remember those details in the video at this point regarding "Owners!D2:D". XLOOKUP doesn't handle dual arrays, at least for now. That's the reason you get only one row. So VLOOKUP is still the function to use for this type of thing.
@@ExcelGoogleSheets Thanks for the response! I figured out the hard way why you couldn't key by 'Joined Tables' since there's a bit of circular dependency. Google sheets suggests the 'solution' of using iterative calculation which i think was too slow with the amount of data I was working with but would probably work fine with less data.
@@ExcelGoogleSheets after watching every single video, google sheets usability, functions were all explained very thoroughly. So in terms of what I think is missing is maybe like “expert” level JS scripting. It’s the only thing I don’t feel advanced in even after watching all your videos. Maybe API integration, and more complex scripting. The rest were really covered in detail!
At 12:22 I get Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 6075. Actual: 5574. So up until then worked well but now cannot resolve this think you fail to say that both join datasets need to be equal in row count
Now you solved my problem. I used to drag the lookup formula to all rows. Thanks a lot (from Brazil)
Fantastic!
This awesome! I have been wanting to do joins for ages. Thank you for these great videos.
It gets a little slow but sure makes it flexible. If I'm not mistaken one could even add new columns to the original tables and it would keep working as long as the new columns get added in between other columns. Otherwise the referenced ranges do not update.
Thank you so much! This is a treasure of knowledge indeed!!!
I am a complete amateur because I don't understand why the 'key' is being shown three times . I guess I should be watching many more of your awesome videos. thank you sir!
Great video. I've avoided arrayformula vlookups with multiple column returns in the past because of performance issues. I think you've even warned against doing it. Anyways, I've opted to just drag down formulas preemptively with the if blank then blank escape to hopefully save on calculations.
Personnaly, I use IFNA to remove NA from the table and
the QUERY with "SELECT * WHERE Col1 IS NOT NULL" to remove empty rows and prevent from adding new empty rows in the sheet which is improving the performance.
Unbelievably incredible, a thousand thank you's!
Glad you enjoyed it!
That's amazing!! I could create a report that I was trying to implement using Data Studio in last 3 weeks! Thanks.
As someone who uses SQL databases and data frames I find it fascinating how difficult a left join is in Google sheets. The creator did a great job figuring that out 😅
Awesome, lots of contents in one single video. I have a doubt: I'm often using IMPORTRANGE function and every now and then I've got to bring within IMPORTRANGE a column with a STRING as a constant, for instance the name of the sheet I am importing it from. How do I manage to do that? Creating a custom array with { } or using QUERY? Thanks in advance!
Any of those should work. QUERY(data,"SELECT Col1, Col4, 'My Sheet'",1)
Loving your videos, very helpful. I have a question that may not be directly related to this video, but I will ask it here anyway, hope you understand. I created 37 sheets with historic price data, instead of typing out the sheet name (other) and the cell on the (current) sheet where I want info onto, I created a list of the sheet names in the first column and attempted to use the cell reference to point to the worksheet. What I get is an error code or N/A or unresolved sheet name.
=(A2)!B14 where A2 refers to the cell on current page where the other sheet name is listed, and !B14 is the cell on the other sheet I want the info from, as example.
Attempted with various quotes etc.
Hope you can help!
czcams.com/video/Vuh7lh2mhQk/video.html
@@ExcelGoogleSheets thanks for getting back to me - I found the video you did on indirect function and it solved my issue. Seriously looked high and low...
watching just one video cleared many of my doubts … thanks bro
Glad to hear
Awesome 👍 I am enjoying your Lesson, Thank you
Glad to hear that!
Your videos are great, thank you much for them!
Thank You!
Sir, your videos is very impressive, I requested you please add also practise files to your video in discription.
Wow the query command is powerful!
What are its limitations? Does it not run fast, or cannot handle large amounts of data?
Depends on what you mean by large, but at some point all arrays in Google sheets have size limits, so when you hit the limit you hit the limit.
Cant find the words. Awesome, thank you.
Thank you!
very helpful, thank you!
Awesome video man. Learned a great deal from this. Liked and subscribed. TY!
Looks so easy when you do it :D
:)
Thank you for an excellent video any chance of showing this solution as code ?
Great, thank you!
Thank you!! Very nice video.
Holy! Thanks man!
:)
Very helpful! Thank you very much!
I am wondering if there is any way to filter a range using =Filter() formula and the condition is that one column should not contain a list of values; something like
=Filter(Sheet1!A:Z , Sheet1!A:ASheet3!$A$1:$A$20). Thank you in advance.
Use MATCH And NOT functions
Could you please make a video showing how to use regular expressions within the find and replace function of Google sheets.
I often find that this might be useful in correcting formulae that I have written that are not using arrayformulas or lookup tables and therefore I need to change each formula by hand
Check these out
czcams.com/video/OVzXpLJRcQk/video.html
czcams.com/video/p5H_xXoGuSs/video.html
Hello,
Great video as usual !
Is there a way to do a proper "LEFT JOIN" with google sheets ?
Sadly no
Sir, I have a google workbook which has sheets with different names, I want to create a single sheet, in which the data of all the sheets is merged.
But only the selected number of coloum from all the sheets are required, which should be connected to the drop down list on the main sheet. what is the full query
Wondering why you key by "Owners!D2:D" in the formula and not just by " 'Joined Tables'!D2:D' "?
otherwise, great video! Was wondering how to achieve a dynamic row count table join with multiple columns (previously used index match)
also wondering if there's any implementation with the new XLOOKUP? I was only able to achieve single row multi column or single column multi row joins so wondering if you had a solution for this.
Sorry, don't really remember those details in the video at this point regarding "Owners!D2:D".
XLOOKUP doesn't handle dual arrays, at least for now. That's the reason you get only one row. So VLOOKUP is still the function to use for this type of thing.
@@ExcelGoogleSheets Thanks for the response! I figured out the hard way why you couldn't key by 'Joined Tables' since there's a bit of circular dependency. Google sheets suggests the 'solution' of using iterative calculation which i think was too slow with the amount of data I was working with but would probably work fine with less data.
This is a bomb! 🔥
Thank you sir
So, is it correct that a nested formula based on this could join multiple tables?
Basically you just need to repeat the VLOOKUP part for more tables. Depending on your next join key it can be fairly simple or quite complicated.
@@ExcelGoogleSheets thanks!
We are not able to see your previous video which was your live session. it is showing as private
Parts of it will be edited and posted for better user experience.
Please post way more! I already seen every video 😫
All 400 of them?! Wow!
@@ExcelGoogleSheets every single google sheets one yes!
I have a question then :) What is left that I have not covered yet? :)
@@ExcelGoogleSheets after watching every single video, google sheets usability, functions were all explained very thoroughly. So in terms of what I think is missing is maybe like “expert” level JS scripting. It’s the only thing I don’t feel advanced in even after watching all your videos. Maybe API integration, and more complex scripting. The rest were really covered in detail!
At 12:22 I get Error
Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 6075. Actual: 5574. So up until then worked well but now cannot resolve this think you fail to say that both join datasets need to be equal in row count
Not working for me, I mean i can not get the array columns to display except column of vlookup
Okay it worked, only If i add Arrayformula
Cool!
Teacher...my Brain hurts
:)