This is the best! Other tutorials were not as concise as yours. This one clears out all the errors you may be making when using the functions! Thank you!
Nice, never really thought that was possible, thanks for show us how. I guess the first IMPORTRANGE can start with A1:G so we don't have to copy and paste headers. Cheers!
=query({ IMPORTRANGE("URL","Name!A4:M"); IMPORTRANGE("URL","Name!A4:M"); IMPORTRANGE("URL","Name!A4:M") }, "SELECT * WHERE Col2 is not null ORDER BY Col2 DESC",0) ASC to arrange in ascending order in place of DESC
I’m not doing this for 100 csv files, god Google is such shit. Microsoft isn’t a great company but even they are smart enough to have a highlight all and “merge as one file” option. Wasting people’s lives away. A parable of Steve Jobs on why Google is shit “During development of the Macintosh computer in 1984, Jobs asked Larry Kenyon, an engineer, to reduce the Mac boot time by 10 seconds. When Kenyon replied that it was not possible to reduce the time, Jobs asked him, "If it would save a person's life, could you find a way to shave 10 seconds off the boot time?" Kenyon said that he could. Jobs went to a white board and pointed out that if 5 million people wasted an additional 10 seconds booting the computer, the sum time of all users would be equivalent to 100 human lifetimes every year. A few weeks later Kenyon returned with rewritten code that booted 28 seconds faster than before.”
This is AMAZING and taught so clearly. Thank you! I'm so glad to see people solving Sheets with functions and queries instead of trying to sell me add-ons (which my company won't let me have).
Would you have any information on IMPORTRANGE limitations, cause I am having issues and can not find clear information about it. Mainly how many spreadsheets can be connected to one, cause I have a MASTER spreadsheet that has all the information that I mainly VLOOKUP from but I ran in to an issue where it starts saying - that I need to have access to that spreadsheet even though I am the owner. After a bit of reading I found out that this happens when there are to many spreadsheets connected.
There is no clear information. And in addition to that they seem to change limits over time. Something that works today might not work tomorrow. So you just end up creating helper files in the middle to dance around those issues.
Freaking smart! Thanks for the video! but.. So im getting error every F****** time I'm hitting enter... I'm doing exactly what you're doing, step by step. the error is "Parse error in formula" So I've tripple checked and stille it gives me error... Any suggestions to what the problem could be?
Great instructional! I have a question though. Lets say I want to combine multiple sheets to a master sheet and each sheet has tabs designating the week of the year(ie: week 1, week 2 thru week 52) and every week I am going to need a new tab in the master that pulls the data from the corresponding week in the other sheets but I don't want to have to edit every formula. Is there a way to use a cell in the master sheet for the tab names(ie week 5). As an example: I have a timecard for john in a sheet with tabs for week 1, week 2 & week 3. I also have a master sheet that pulls all employees timecards and has tabs for week 1, week 2 & week 3. somewhere in each tab on the master sheet a cell indicates the name of the tab(ie week 1) which is referenced in the formula to merge the data from John's timecard week 1. This way I can copy the tab & just change the data in that cell to "week 2" for example and the formula will now draw the data from Johns timecard tab for week 2? I hope the explanation makes sense & I greatly appreciate your input.
Hi I can online combine 2 excel sheet, but I got error on the 3rd, it says (In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.). Please help
i try to create data like this , but when i add more data in to the first or the second partial sheet , then the Master file do not increase the data ??? why not ???
={ FILTER('NEW DAILY DIARY'!B4:I,'NEW DAILY DIARY'!C4:C=B1); FILTER('BHAVESH DIARY'!B4:I,'BHAVESH DIARY'!C4:C=B1) } THIS GIVE ERROR WHEN THERE IS BLANK IN DATA SHEET, ANY SOLUTION? THERE IS BLANK IN DATA SHEET, IS THAT A REASON
No comment - just a technical question Year after year I am creating a new spreadsheet with 12 sheets for every month of the year. Up to now I always made a spreadsheet copy and started to eliminate all the values of the preceding year - not the formulas of course. That's a tedious job and could not find any YT Instruction for such an easy ? question: "How to copy a new yearly spreadsheet with just the formulas, not the values ?" Any hint greatly appreciated Terence Cackett
Could anybody tell me what's the difference betweeen this way and the Combine Data from Multiple Google Sheets Documents (Workbooks) in Google Drive into One Workbook? I mean, which are the things that one allows and the other doesnt?
I an learning so much with your videos, they're really great. Is it possible to feed the partial data sheets from the master sheet? For example, imagine that I add a column in my master sheet that I want it to show in my partial sheets. I've tried to use the vlookup + importrange that you've talked about in another video and it does work but when the master is updated with new rows the column that I added in is obviously not going to match. Thank you in advance! ^_^
I have one more query... Is it possible to know the data in the Master Sheet belongs to which Sheet as I have managed to Get all my Six (Group_1-6) files to ome Master File ... But now the problems is that non of the Six File have Name in any Header ... By which we can know the data is of which Group file... Let me know if there is any suggestions or solution to this... Thanks
Thanks for the lesson and tips just a quick question...What if I do not need to import a certain column(s)? let say in your example the "Sales_Rep" & "state" is there a syntax to formulate in this "importrange"? Thank in advance
Is there a way to just simply combine multiple sheets into one workbook, not merging it into one sheet? For example, if we have 3 different report in google sheet, can we just have one workbook with 3 reports but just in each tab - making it one workbook with 3 tabs? (Using g script).
Hey, I have a question, let's say I have A3 added from a specific 'child' sheet, in my master sheet; let's say I wanna do a change to the 'sales' tab, for row A3 in the master sheet, how can I do that change so it will reflect on the 'child' sheet without throwing "REF" error in the master sheet? Thank you
this is very well put together. Thanks for the tutorial! Do you have video about linking data from different files in Excel and GSheets? I am struggling a bit with Excel since they don't have IMPORTRANGE. And I wish to know some ways to feed the live data acros different Excel files stored on OneDrive. And if there is a way to do the same between OneDrive files and Gdrive sheets, that would be so cool!
Thanks. That's great! But what if you have ranges you want to blend into the same rows, with a shared unique ID. For example, someone's personal info from one source, and sales figures from another source?
Thank you for all your videos!! Awesome! I'd love to figure out how to create a master with sheets that do not have the same headers (e.g. Google Form populates the responses with the question as the header. Also, how the master can have the columns mixed, instead of grouped by source sheet. Thanks!
If there are many files (eg 100 files) with many sheets of the same structure in the same folder, merge data into 1 sheet of 1 file, using Google Script, is it possible?
Help pls! I combined 4 sheets but now on the master sheet there’s one column that has mainly dates but there are some text/string in that column. The only data that’s shown are dates but the text fields within the column are blank. Do you have a video on this??? How can this be solved? In the case of mixed data types what to do? Pls help
Hi. Im from Brazil. I must say your video was grate! More then that it saves a lot of work and trouble trying to learn apps scrips. Thank you so much for this great class. U earn a follower.
Very useful and makes my work much easier! i was just wondering if anyone can answer this, is it possible to do the query + import range formula (so there wont be any blank rows in combined datas) if the datas are still on going datas? (means i do this import ranges from multiple sheets to have an automatic updates from multiple sheets)
Hello, thank You for awesome video. I want to combine multiple Google Sheets with different columns (use some of the columns). Is there any way to do this?
I'm try use query() formula at 10:07 minute in this video and change "SELECT * WHERE Col1 is not null" to be "SELECT *" for intent to get all consolidate record but the consolidate sheet return only DataSet1. Have you any advise?
You imported all the cells, including empty. If You scroll down, You'll see data from other table as well. That is why it is better to use 'is not null'
I tried it Sir, it was good until I added the 3rd Filter-Importrange formula, the error is in ARRAy_LITERAL, missing values for one or more rows, how can I fix it Sir..?
Hi. I tried both. But I still got the "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." error. Is there something that I should do?? Hoping for your response. Thank you.
same here, i tried to combine 28 sheets "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." is that because g.sheet has maximum number sheet to combine ?
hi how can i consolidate multiple worksheets without affecting the existing row /details in the final sheets.,coz i have this reports that i need to consolidate files from different people ( same excel sheet details).need to combined in one file ..without deleting or affecting other data per row.
Depends on what you need. FILTER is a good utility function in sheets, works best when you need the results used within a different function. QUERY is more of outer wrapper function, less likely to be used within a different function.
thanks a lot what i almost seen in the video was just consolidating...what if the excel file had existing data included but i need to copy other details from other sheets..( they have same template, encoded by different people ) thank you.
Very cool thanks for sharing!Is it possible to add a column to the master sheet where the cell would reference which workbook the data was pulled from?
Is there a way to sort columns after you use this formula? Using the filter feature, it only sorts the first data set. Thank you for this very helpful video!
hi i have a problem i have 3 sheets and i want to combine them in one sheet but the problem is if one of the sheets is empty the master sheet is not working i am using filter importrange . can you help me the find the solution for this but the query import is not working for me
try to move filter function outside of arrays combinations. In other words, combine the arrays like in QUERY example, but filter the data using FILTER function instead of QUERY.
Hi, Nice work there! Is there any way to make it additive? I mean like each week I get a new file and I want to combine it with the previous data. Previous data will not need to be updated but just merge the new data from the new file.
I e-mailed you via your business email a few days ago, but havent heard anything from you. yet. So I want to leave my message here if you can read it here better than in your e-mail. My name is Yohan Kim. First, I want to say thank you for the videos in the Channel. I have learned a lot from you and solved many problems when I was coding. I am not a programmer but a middle-high school(K6-12) English teacher in South Korea. I had to develop a SIS(School Information System) for my school because my school is a small school, not supported by the Educational Ministry. So I started to develop it with Google spreadsheet by learning from many resources in English. There are not many materials about google apps script in Korean. Fortunately, I am an English teacher. After a year, I made it and my school uses it, and I gave it to many other small schools. However, I am still learning and developing it. Since the apps script is not very well known in Korea, I'd like to try to upload videos in RUclips like you have done. I'd like to ask you if I can watch your video and record similar videos in my version(Korean style and language) and upload them in my RUclips Channel. I will make a new youtube channel for it. Of course, I will tag your channel, saying "this is the original youtube Channel I learned about this technique. If you are able to understand English, you can go to the channel and learn a lot and better." If you allow me to do that, I will make upload videos similar with your videos in Korean. If you don't approve it, I will try make my own curriculum. I will look forward to hearing from you. Thank you again for your hard work in RUclips.
@@ExcelGoogleSheets Thank you. I am not good at this skill. I've never learned any programming in regular curriculum. I still need to learn from your videos a lot. I have watched your videos even less than 15. So my plan is to watch your video and make note and do the same thing to learn it for myself. It may help me keep learning (one of motivation or kind of resolution)
Thanks for this video, Its really helpful. But What if there is huge amount of data, like 20K+ rows. I tried to do it for 1.5K rows and 28 Columns and it was taking forever to import.
I'm running into an issue where Error: Result too large pops up when I try to import from another spreadsheet. The source spreadsheet has 11 columns with 39,000 rows of data. Is there any way to work around this? Thank you & channel subscribed!
@@ExcelGoogleSheets Thank you for responding so quickly. That's exactly what I've done. I was hoping for a more elegant solution. Thanks again and please keep the tutorials coming!
Hi Cavit. Yes, it is possible but you might have to wrap each importrange inside a query, then add the column on each query identifying the entries of that particular importrange..... a bit confusing: something like this: QUERY({QUERY(IMPORTRANGE("spreadsheet1_url", "World Cup1!A1:D21"), "select Col1, Col2, Col3, Col4, 'World Cup 1' where Col1 is not null ",1);QUERY(IMPORTRANGE("spreadsheet2_url", "World Cup2!A1:D21"), "select Col1, Col2, Col3, Col4, 'World Cup 2' where Col1 is not null",1)}, "select * where Col1 is not null label Col5 'Source_Table'",1)
Wonderful! Question: I am combining data and I want to sort the dates in descending order. My problem is that the sheets are from different years and it seems to be sorted by the first character in the date (the month) and not in actual chronological order. Is there a way to fix that?
That means google doesn't recognize those as dates, it sees them as text. Try to go to each data table an format the column as date, see if that helps.
Wow! Your explanation is very easy to understand. Great job! Thank you.
This is the best! Other tutorials were not as concise as yours. This one clears out all the errors you may be making when using the functions! Thank you!
ok now this Problem is Solved
The DATA was OverLapping in the BLANKS
I deleted all the Empty Rows and its Working Now
Nice, never really thought that was possible, thanks for show us how.
I guess the first IMPORTRANGE can start with A1:G so we don't have to copy and paste headers.
Cheers!
You could, but I like to keep my data separate.
the datas must be keep separate always, because you must work on them. When you sort for example, you sort with the headers, so its not nice,
=query({
IMPORTRANGE("URL","Name!A4:M");
IMPORTRANGE("URL","Name!A4:M");
IMPORTRANGE("URL","Name!A4:M")
},
"SELECT * WHERE Col2 is not null ORDER BY Col2 DESC",0)
ASC to arrange in ascending order in place of DESC
I’m not doing this for 100 csv files, god Google is such shit. Microsoft isn’t a great company but even they are smart enough to have a highlight all and “merge as one file” option.
Wasting people’s lives away. A parable of Steve Jobs on why Google is shit
“During development of the Macintosh computer in 1984, Jobs asked Larry Kenyon, an engineer, to reduce the Mac boot time by 10 seconds. When Kenyon replied that it was not possible to reduce the time, Jobs asked him, "If it would save a person's life, could you find a way to shave 10 seconds off the boot time?" Kenyon said that he could. Jobs went to a white board and pointed out that if 5 million people wasted an additional 10 seconds booting the computer, the sum time of all users would be equivalent to 100 human lifetimes every year. A few weeks later Kenyon returned with rewritten code that booted 28 seconds faster than before.”
HI,..Let's say you have sales and quantity in different workbook,.how will you divide /multiply sales and quantity in a third master workbook,...
Thanks for the step by step tutorial, very very very helpfull. Cheers!
HI
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.Getting this error in final stage
HI
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.Getting this error in final stage
This is AMAZING and taught so clearly. Thank you! I'm so glad to see people solving Sheets with functions and queries instead of trying to sell me add-ons (which my company won't let me have).
When I update in master file, it seems like all data is gone. Can't do any changes on it. Can we have some formula that links both ways??
How do I include hyperlinks since the hyperlink is removed automatically.
Would you have any information on IMPORTRANGE limitations, cause I am having issues and can not find clear information about it.
Mainly how many spreadsheets can be connected to one, cause I have a MASTER spreadsheet that has all the information that I mainly VLOOKUP from but I ran in to an issue where it starts saying - that I need to have access to that spreadsheet even though I am the owner.
After a bit of reading I found out that this happens when there are to many spreadsheets connected.
There is no clear information. And in addition to that they seem to change limits over time. Something that works today might not work tomorrow.
So you just end up creating helper files in the middle to dance around those issues.
Brilliant 👍 thanks man i learned a lot from you and I hope you start a SQL chanal.
There will be SQL videos coming with SUPERSQL function.
Freaking smart!
Thanks for the video!
but.. So im getting error every F****** time I'm hitting enter...
I'm doing exactly what you're doing, step by step.
the error is "Parse error in formula"
So I've tripple checked and stille it gives me error...
Any suggestions to what the problem could be?
Maybe this? ruclips.net/video/ljvB2arCsNQ/видео.html
This is very clearly and it works for me but i put data validation in master file very thing goes wrong.
How to block cell selection in google sheet like "Microsoft excel protect lock cell option". Could you please explain the trick
Great instructional! I have a question though. Lets say I want to combine multiple sheets to a master sheet and each sheet has tabs designating the week of the year(ie: week 1, week 2 thru week 52) and every week I am going to need a new tab in the master that pulls the data from the corresponding week in the other sheets but I don't want to have to edit every formula. Is there a way to use a cell in the master sheet for the tab names(ie week 5).
As an example: I have a timecard for john in a sheet with tabs for week 1, week 2 & week 3. I also have a master sheet that pulls all employees timecards and has tabs for week 1, week 2 & week 3. somewhere in each tab on the master sheet a cell indicates the name of the tab(ie week 1) which is referenced in the formula to merge the data from John's timecard week 1. This way I can copy the tab & just change the data in that cell to "week 2" for example and the formula will now draw the data from Johns timecard tab for week 2?
I hope the explanation makes sense & I greatly appreciate your input.
Dear sir. Can you help me with e google sheet document? I'm stuck.
Hi I can online combine 2 excel sheet, but I got error on the 3rd, it says (In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.). Please help
may i know what is the equivoloent in Excel for this please?
i try to create data like this , but when i add more data in to the first or the second partial sheet , then the Master file do not increase the data ??? why not ???
={
FILTER('NEW DAILY DIARY'!B4:I,'NEW DAILY DIARY'!C4:C=B1);
FILTER('BHAVESH DIARY'!B4:I,'BHAVESH DIARY'!C4:C=B1)
}
THIS GIVE ERROR WHEN THERE IS BLANK IN DATA SHEET, ANY SOLUTION?
THERE IS BLANK IN DATA SHEET, IS THAT A REASON
what if i want to import all the columns that is dynamically updated along with the rows.
hey , why alpha-numeric cells are coming blank please reply, it ll help me a lot.. for example : U100ML81041030, this cell comes as a blank..
Select the source column and change it to text type.
Otherwise use my other video with FILTER function instead.
No comment - just a technical question
Year after year I am creating a new spreadsheet with 12 sheets for every month of the year. Up to now I always made a spreadsheet copy and started to eliminate all the values of the preceding year - not the formulas of course. That's a tedious job and could not find any YT Instruction for such an easy ? question: "How to copy a new yearly spreadsheet with just the formulas, not the values ?"
Any hint greatly appreciated
Terence Cackett
Could anybody tell me what's the difference betweeen this way and the Combine Data from Multiple Google Sheets Documents (Workbooks) in Google Drive into One Workbook? I mean, which are the things that one allows and the other doesnt?
I an learning so much with your videos, they're really great.
Is it possible to feed the partial data sheets from the master sheet?
For example, imagine that I add a column in my master sheet that I want it to show in my partial sheets.
I've tried to use the vlookup + importrange that you've talked about in another video and it does work but when the master is updated with new rows the column that I added in is obviously not going to match.
Thank you in advance! ^_^
Use QUERY or FILTER functions.
I have one more query...
Is it possible to know the data in the Master Sheet belongs to which Sheet as I have managed to Get all my Six (Group_1-6) files to ome Master File ...
But now the problems is that non of the Six File have Name in any Header ... By which we can know the data is of which Group file...
Let me know if there is any suggestions or solution to this...
Thanks
Thanks for the lesson and tips just a quick question...What if I do not need to import a certain column(s)? let say in your example the "Sales_Rep" & "state" is there a syntax to formulate in this "importrange"? Thank in advance
Is there a way to just simply combine multiple sheets into one workbook, not merging it into one sheet? For example, if we have 3 different report in google sheet, can we just have one workbook with 3 reports but just in each tab - making it one workbook with 3 tabs? (Using g script).
Hey, I have a question, let's say I have A3 added from a specific 'child' sheet, in my master sheet; let's say I wanna do a change to the 'sales' tab, for row A3 in the master sheet, how can I do that change so it will reflect on the 'child' sheet without throwing "REF" error in the master sheet? Thank you
🔥This is just way too cool. THANKS FOR YOUR EFFORTS. IT IS REALLY APPRECIATED especially your tips for organizing the formula via Alt Enter🔥
Hello
Nice explanation.........
How do I give access as the dialogue box says learn more and Allow access is not showing......
Pls guide.
Kindly help I'm getting an error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
this is very well put together. Thanks for the tutorial! Do you have video about linking data from different files in Excel and GSheets? I am struggling a bit with Excel since they don't have IMPORTRANGE. And I wish to know some ways to feed the live data acros different Excel files stored on OneDrive. And if there is a way to do the same between OneDrive files and Gdrive sheets, that would be so cool!
You are the best bro ! thank you so much !!
No one can see this edit or use it except me ? I need this edit to appear to people who are using this sheet ! Please i need answer
Is there a way to import so that formatting of the cells matches? We use many color-coding systems and this would be super helpful!
i think that working with app script may solve your prlbm
What can I do when Error: Too large Data. appears?
Thanks. That's great! But what if you have ranges you want to blend into the same rows, with a shared unique ID. For example, someone's personal info from one source, and sales figures from another source?
Thank you for all your videos!! Awesome! I'd love to figure out how to create a master with sheets that do not have the same headers (e.g. Google Form populates the responses with the question as the header. Also, how the master can have the columns mixed, instead of grouped by source sheet. Thanks!
If there are many files (eg 100 files) with many sheets of the same structure in the same folder, merge data into 1 sheet of 1 file, using Google Script, is it possible?
This is exactly what I needed! Googling and AI couldn't solve my problem but you did it perfectly!
Thanks love your work..
Sir please help me, how to sum only sale column??
Help pls! I combined 4 sheets but now on the master sheet there’s one column that has mainly dates but there are some text/string in that column. The only data that’s shown are dates but the text fields within the column are blank. Do you have a video on this??? How can this be solved? In the case of mixed data types what to do? Pls help
Hi.
Im from Brazil.
I must say your video was grate! More then that it saves a lot of work and trouble trying to learn apps scrips.
Thank you so much for this great class.
U earn a follower.
i am trying this but its showing as "Adding permissions" with loading symbol...but its not ...can you please let me know what am i doing wrong
Thank you so so so much sir!!! 🙏🙏🙏🙏
Watching your videos made my difficult task very easy, I ❤ U Sir.
when it is showing error there is no allaw access choice
What is you have 100 client workbooks that you want to combine together into one master?
Very useful and makes my work much easier! i was just wondering if anyone can answer this, is it possible to do the query + import range formula (so there wont be any blank rows in combined datas) if the datas are still on going datas? (means i do this import ranges from multiple sheets to have an automatic updates from multiple sheets)
how to hyperlink a sheet to another workbook?
If a cell is larger on the original document than normal, how can you get the master to accurately depict so all the text shows?
kindly provide practice sheet
can you provide the script?
1 bilion thanks
:)
If my sheets are color coded are, is there a way to bring the colors over to the master as well?
No, not with a formula.
Awesome! You have explained very well and sorted my very big problem in which I was stuck form last 4 days. Thanks so much.
thank you very much for sharing this. It helped me a lot
When I use importrange the data added to the next colum not next row. How can I solve this problem? Thanks
Probably worth to watch this ruclips.net/video/ljvB2arCsNQ/видео.html
@@ExcelGoogleSheets Thanks it's works like a charm and your explanation is perfect🙏🙆♂🙇♂🙆♂🙇♂
Thank you so mch
19:52 - my life with google sheets
Hello, thank You for awesome video. I want to combine multiple Google Sheets with different columns (use some of the columns). Is there any way to do this?
It's possible, but it's very messy and complicated.
This video has the overall idea how to do it and you need to combine it with bunch of importrange functions ruclips.net/video/CVkyfGUSHeg/видео.html
@@ExcelGoogleSheets Thank You so much for the reply :)
Hi when the files are updated with new data will the master file automatically update??
Awsome trick and its solved my issue, thanks dude
how can we do this using app script
i m not getting allow access
away with what setups can generate.
I'm try use query() formula at 10:07 minute in this video and change "SELECT * WHERE Col1 is not null" to be "SELECT *" for intent to get all consolidate record but the consolidate sheet return only DataSet1. Have you any advise?
You imported all the cells, including empty. If You scroll down, You'll see data from other table as well. That is why it is better to use 'is not null'
I tried it Sir, it was good until I added the 3rd Filter-Importrange formula, the error is in ARRAy_LITERAL, missing values for one or more rows, how can I fix it Sir..?
It can't be empty.
Hi. I tried both. But I still got the "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." error. Is there something that I should do?? Hoping for your response. Thank you.
same here, i tried to combine 28 sheets
"In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."
is that because g.sheet has maximum number sheet to combine ?
hi how can i consolidate multiple worksheets without affecting the existing row /details in the final sheets.,coz i have this reports that i need to consolidate files from different people ( same excel sheet details).need to combined in one file ..without deleting or affecting other data per row.
It sounds like you may need to use scripts.
Overall Compare between Filter and Query function which one is better? Thank you.
Depends on what you need. FILTER is a good utility function in sheets, works best when you need the results used within a different function. QUERY is more of outer wrapper function, less likely to be used within a different function.
Bonjour, merci pour votre aide , comment faire une jointure entre plusieurs feuilles ?
I liked your voice work on Despicable Me
thanks a lot what i almost seen in the video was just consolidating...what if the excel file had existing data included but i need to copy other details from other sheets..( they have same template, encoded by different people ) thank you.
I think you might need VLOOKUP function, but it's not exactly clear what you're trying to do.
You are a lifesaver! Thank you so much for this tutorial! I might finally be able to submit my project and sleep early tonight haha You are great!
Fantastic!
Very cool thanks for sharing!Is it possible to add a column to the master sheet where the cell would reference which workbook the data was pulled from?
Yes, but it would be a very convoluted formula.
This video should give you some clues ruclips.net/video/DDlh3EN6NV8/видео.html
Is there a way to sort columns after you use this formula? Using the filter feature, it only sorts the first data set. Thank you for this very helpful video!
No. You can use SORT function to autosort your data though.
If you want to manually sort it, then you'll need to copy/paste values someplace.
hi i have a problem i have 3 sheets and i want to combine them in one sheet but the problem is if one of the sheets is empty the master sheet is not working i am using filter importrange . can you help me the find the solution for this but the query import is not working for me
try to move filter function outside of arrays combinations. In other words, combine the arrays like in QUERY example, but filter the data using FILTER function instead of QUERY.
THANKS!
May I get the file containing the formula?
Appreciated Man! Thanks :)
Thank you so much!
You explaining every video so nicely. You are so genius.
why i cant import my data.. error formula
Hi, Nice work there!
Is there any way to make it additive?
I mean like each week I get a new file and I want to combine it with the previous data.
Previous data will not need to be updated but just merge the new data from the new file.
thanks!
I e-mailed you via your business email a few days ago, but havent heard anything from you. yet. So I want to leave my message here if you can read it here better than in your e-mail.
My name is Yohan Kim.
First, I want to say thank you for the videos in the Channel.
I have learned a lot from you and solved many problems when I was coding.
I am not a programmer but a middle-high school(K6-12) English teacher in South Korea.
I had to develop a SIS(School Information System) for my school because my school is a small school, not supported by the Educational Ministry.
So I started to develop it with Google spreadsheet by learning from many resources in English. There are not many materials about google apps script in Korean. Fortunately, I am an English teacher.
After a year, I made it and my school uses it, and I gave it to many other small schools.
However, I am still learning and developing it.
Since the apps script is not very well known in Korea, I'd like to try to upload videos in RUclips like you have done.
I'd like to ask you if I can watch your video and record similar videos in my version(Korean style and language) and upload them in my RUclips Channel. I will make a new youtube channel for it.
Of course, I will tag your channel, saying "this is the original youtube Channel I learned about this technique. If you are able to understand English, you can go to the channel and learn a lot and better."
If you allow me to do that, I will make upload videos similar with your videos in Korean.
If you don't approve it, I will try make my own curriculum.
I will look forward to hearing from you.
Thank you again for your hard work in RUclips.
I don't know what to answer. On one hand I want to say yes, on the other hand you are a teacher, why not create your own content?
@@ExcelGoogleSheets Thank you. I am not good at this skill. I've never learned any programming in regular curriculum. I still need to learn from your videos a lot. I have watched your videos even less than 15. So my plan is to watch your video and make note and do the same thing to learn it for myself. It may help me keep learning (one of motivation or kind of resolution)
Which is faster? Filter vs Query
This is GR8!
Thanks for this video, Its really helpful. But What if there is huge amount of data, like 20K+ rows. I tried to do it for 1.5K rows and 28 Columns and it was taking forever to import.
Try Google Colab method ruclips.net/video/x2Cr9hi3MnY/видео.html see if it's faster.
I'm running into an issue where Error: Result too large pops up when I try to import from another spreadsheet.
The source spreadsheet has 11 columns with 39,000 rows of data. Is there any way to work around this?
Thank you & channel subscribed!
Try doing multiple importrange formulas from the same spreadsheet and use first say 10K rows in the first and the next 10K rows in the second, etc.
@@ExcelGoogleSheets Thank you for responding so quickly. That's exactly what I've done. I was hoping for a more elegant solution. Thanks again and please keep the tutorials coming!
Too much 😑boring
hello, is it possible to add a column in the "Master File" to identify the spreadsheet which data come from?
Hi Cavit. Yes, it is possible but you might have to wrap each importrange inside a query, then add the column on each query identifying the entries of that particular importrange..... a bit confusing:
something like this:
QUERY({QUERY(IMPORTRANGE("spreadsheet1_url", "World Cup1!A1:D21"), "select Col1, Col2, Col3, Col4, 'World Cup 1' where Col1 is not null ",1);QUERY(IMPORTRANGE("spreadsheet2_url", "World Cup2!A1:D21"), "select Col1, Col2, Col3, Col4, 'World Cup 2' where Col1 is not null",1)}, "select * where Col1 is not null label Col5 'Source_Table'",1)
Wonderful! Question: I am combining data and I want to sort the dates in descending order. My problem is that the sheets are from different years and it seems to be sorted by the first character in the date (the month) and not in actual chronological order. Is there a way to fix that?
That means google doesn't recognize those as dates, it sees them as text. Try to go to each data table an format the column as date, see if that helps.
@Learn Google Sheets & Excel Spreadsheets I tried that but I'll try it again.