Combine Multiple Spreadsheets Into One in Google Sheets
Vložit
- čas přidán 2. 07. 2024
- Sure, you can use ChatGPT to write you a script to combine all these together, but where's the fun in that? Well, I guess prompt generation is fun. But still, this is where Google Sheets blows its competition out of the water - especially if you're still using Excel 2016 like I am!
----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Intro
0:50 Structures
4:00 Combining the data
8:30 Combine multiple workbooks
----------------------------------------------------------------------------------------------------
💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓ - Jak na to + styl
Thank you very much! Very easy to follow and I liked your screen casting as well🎉
Thank you for simple explanation. You make me happy!
Wow this was truly helpful as I’m building out a product that incorporates multiple members to keep me updated on the status of our business. I’ve incorporated this and I must say that it work flawlessly. Every day I learn something new and I am glad that I came across your channel. Thanks again for your tutorial.
Love it! Keep up the hard work, Michael!
I love you! Thank you for making my life easier lol I didn't want to be forced to learn how to use scripts to connect different spreadsheets together :))))) I just came here to say that.
Always happy to help!
Thank you very much! Very helpful!
Broh, You are outstanding 🎉
14:07-14:33 is my favorite part of this video
That shocked realisation 🤣🤣
😂😂😂
@@HashAliNZ totally awesome, but how to make it auto without handle Ctrl+C/V ?
how to for combine sheets list(dynamic) of sheets just by formulas?
@@layymer once this is setup, it is fully automated and dynamic because of the importrange functions. To make it more dynamic, let's say by automatically adding extra tabs from files in the same folder, would require a script. A video for that process is currently in the works
Hi, thank you for your video, may I know how I can separate each IMPORTRANGE data by one extra row?
Thank you so much, this help my life a lot better 😄
Thank you, very useful.
Great !!!! this is cool as heck
FABULOUS!!!! Thank you very much!
Happy to help!
Wow! Great tricks to cut tedious work in the end 😍
Yep - I've got some tips coming up to make it even more automated.
@@HashAliNZ ready to learn more so 😎
GREAT STUFF !
thank you very helpful
Sir ur really great... Amazing teaching and tricks...
Thank you so much for very helpful video.. m big fan u sir..❤
It's my pleasure!
Thank you so much.
Thank u very much! Because there are many people teaches how to combine 3 sheets , but no one tell them how to combine more sheets
Happy to help, Ashley!
Did you spot the uncorrected error? It takes a good eye! Let everyone know what you saw!
Game changer!!! thank you!!
Happy to help!
Hi, I have a question. If I edit on one of the workbook, will the combine workbook also update that edit? Thankiu
Will be work to combine multiple workbooks if it have different columns?
Question: Using your example at 16:02 in the video, lets say I have additional data to add to each row on this master sheet after you have combined the workbooks....each time a new sheet (month/year) is added the new data in the master sheet will not correspond to the correct row. How can this be fixed?
awesome thanks!!!
hello! if for instance, I want to have an extra column to note what months these data were from, is there an easy way of doing it?
At 7 minutes I followed how to combine sheets (which is awesome) into a master sheet & avoid the blanks....but then how can I sort the master without messing up the formulas? Exmple....then in the master I want the master sheet to always sort by dollars sold highest to lowest that it pulled from all the sheets....is that possible
Brilliant, thank you very much. One question please. If I am bringing in 4 sheets and wish the sheet name (or associated text relating to that sheet) to be shown in new column after the imported data is there a way to do that? I hope that makes sense but if not: if each sheet relates to an area then I would like to import the standard columns in each of the sheets and then add the area that the sheet refers to?
Sorry, I'm not sure what you're asking. I think I'd need to see an example
Great video thanks - as this is dynamic ie the master changes then the copy updates - is it possible to rework this so it only imports once - maybe on a button press - so it does not slow down each time a colleague changes anything in the master - but can at least make 1 / 2 copies per day?
Yeah that's doable with a script. You could either set it up as a button or you could put a time trigger on it.
Really helpful video, Subscribed right away! Wanted to ask - my headings in the source spreadsheets are in rows, but I am looking to list them into columns in the master spreadsheet. Is there a way to do that? Sorry if I sound rude in any way, I have less than 12 hours and have more than 500 sheets of data, your prompt response/help would be a massive help. Thanks in advance!
Absolutely! We can use the =TRANSPOSE() function to turn the data from rows into columns. For the 500 sheets, it's best to do it with a script similar to the second half of this video, but you might need to make a few adjustments based on your specific case.
@@HashAliNZ thank you! Really appreciate your response on this
This is great, What if we want to sort the master sheet by Date or Number ascending/descending in a specific column? can it be done?
Absolutely! At the end of the query, just add 'order by Col2 desc/asc'. For example:
=QUERY({Datasets},"Where Col1 is not null order by Col2 desc")
to sort by column 2 from biggest to smallest. If you want from smallest to biggest, use asc instead of desc. You can have multiple sorts in there too:
=QUERY({Datasets},"Where Col1 is not null order by Col2 desc, Col1 asc")
Would it be possible to combine multiple sheets into one master sheet. And the master sheet auto update when those multiple sheet is updated?
Yep, this method does exactly that. Whenever something changes in the independent tabs or workbooks, the Master (which I labelled 'Raw Data') will automatically update
This video was amazing.
Thanks so much, Eric!
@@HashAliNZ I became an instant sub. I've watch countless CZcams videos but yours was the best. Thank you for sharing your knowledge. Looking forward to more amazing videos.
Thanks for the support! I'll get them out when I can ❤️
I'm trying to combine sheets where the new data is entered in columns rather than rows but when I'm following along using what youve shown abve its just putting the next data set into new rows so throwing everything out. hope that makes sense
Hi Michael,
It's generally better to add new data in rows instead of columns, but if that's not possible for whatever reason here's a fix.
At 3:19 I use A2:G. For columns, you would use A2: 7, or however many rows you have. The way when a new column is added, it'll copy of the every cell from A2 down to row 7.
Hi! I have an app script that automatically creates new tabs based on data inputs and was wondering if there’s a more dynamic formula that is able to merge data from new tab into the master sheet as well. thanks!!
It wouldn't be fully dynamic because you'd need to reference the name of the new tab. I guess you could do it with an indirect function after listing out all the tab names.
My Favourite
Hi, Thank you so much for your videos they are great! However, I have a question. I'm trying to combine different sheets into one master sheet, but this sheets have tabs how would and in the example that you give you don't mention that, how would that work?
Hi Silvia, Do you mean you have lots of tabs and one master sheet you want to collate everything into? As long as the structures on all the tabs are the same, you can do =query({tab1!A1:K;tab2!A1:K, tab3!A1:K},"Where Col1 is not null")
Copy and paste that formula into your spreadsheet, then replace the tab names and the ranges with your needs
Hi @@HashAliNZ , thanks for responding, I have 10 sheets with different names (1,2, 3, etc..)each sheet have different tabs (a, b, c, ect..) about 6-10 each, some same name some different. I want to have a master sheet using only one of the tabs that are the same name from all the sheets. Let's say tab b is the same name in all sheets, that's the one I want to combine would it be possible with that query?
You are great😊
Thank you so much 😀
Is there a way to 'allow access' for all the sheets in one go? I had 579 sheets and it took a lot of time(an hour maybe) to allow access for every single one individually. It would be really helpful for those who work with a lot of sheets on a regular basis (if they want to combine them).
Yep! Pop all the files into one folder in your Drive, then set the permission of that file to allow access to whatever you want: view only or Edit access
Hello, thnk you for this - the query function is working, but it is not pulling all of the data from the other sheets, only some of the data. I am not sure why?
Hey Jerome, It's difficult to tell without seeing the spreadsheet or the formula. Feel free to paste your formula here and I'll see if I can spot any problems
Can I change the data in the combined sheet? For example, if I have some errors in one table and I cant go to that specific table, can I do it in the combined table so the changes reflec everywhere?
Nah, make the changes to your data in the original spreadsheet. Combining all your data should be kinda like a data dashboard for reviewing information
Very Handy. For me i like the Sheet per month limiter, but you say google sheets is more effecient with pulling data from a single sheet. That seems to be a tip that underlies efficient spreadsheets. I guess the question is to broad, but how in the world is a medium user to know what is an effiecient way to set up a spreadsheet project?
sir how we take its total ?
Thank You, So, when I do the second sicario and iportrange I get the #REF! and I get a ERROR "cannot find rang or sheet for imported range."
Never mind this comment I found my issue, I didn't understand that it had to be 'TAB' name and not any other reference
can you combine different combine multiple sheet into one,but have different column?
Sure. Just tell the query which column you want. For example
=query({Sheet1!A2:F;Sheet2!A2:F},"Select Col3 where Col3 is not null")
@@HashAliNZ like sheet 1!a2:f;sheet2!a2:g
it is possible?
Any way you can make this even easier, especially if you have > 80 sheets?
Well, I find this super easy and I can get this done within five minutes, but I've been working on client's spreadsheets like these for years so have the experience.
There is a way this can be done with a script and it's very simple if you know how to write scripts, but it'll need to be customised for your setup.
SIR HOW CAN WE ADD SAME ITEMS PURCHASE AND SALE FROM DIFFERENT SHEETS TO ONE MASTER SHEET TO GET THE ACTUAL STOCK OF ITEMS ?
Use a sumif function to add together products with the same SKU
Is there an easier way to copy the links of the worksheets? I have more than 500 sheets and copying their links one by one will going to take me ages! Please help!
Absolutely! Open up a new spreadsheet within the folder that has all your Google Sheets. Click on Extensions > Apps Script. Delete the default function, then paste this in:
function getSSIDs() {
const folderID = '1CVEzhKbeupe_6zP4uoRnMhPNcoMFUFWD';
const folder = DriveApp.getFolderById(folderID);
const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
let spreadsheetIds= [];
while (files.hasNext()) {
let file = files.next();
spreadsheetIds.push(file.getId());
}
return spreadsheetIds;
}
function writeSSIDs() {
let spreadsheetIds = getSSIDs();
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clear();
for (let i = 0; i
If you need to see exactly how to extract the IDs, I made this video just for you: czcams.com/video/-csVP1OjRCw/video.html
@@HashAliNZ can't thank you enough for this! I am glad that I stumbled upon your channel.
If you ever need help related to smartphones, earphones or smartwatches just let me know, I'll help you out.
👍💯