Thank You. Never wanted in my life. Now when my 10 year old wanted a record of her read books (from library/borrowed/own/kindle/audible), i thought of having different tabs and getting the result in a final one. This helped a lot ! Thanks again

This is helping me so much. My only problem is now I need to sort some data now that it's all complied, but it's sorting it within each array when I want it to sort through all of the data together (I don't even know if that makes sense). Basically I'm pulling data from 5 sheets into 1, and now I'm trying to sort highest scoring to lowest scoring overall, not from within each separate sheet.

I just need to tell you this video is piece of art. Extremely valuable.

This is a great video... one important thing to note... the column reference in your query is case sensitive. col1 will not work, but Col1 works :)

Finally - a methodical - clear tutorial style. Thank you, and keep up the good work.

very useful step by step even after 6 years! greetings from Malaysia and bless you for the talent.

why would anyone thumb down, thanks mate for the video, great help

Rather than the giant "or" statement, you could make a helper column which contains a COUNTA formula which counts the number or cells in the range containing data, then filter by those where it doesn't equal 0, or if you wanted to use null, you could force null if the COUNTA = 0

Thank you a million. Frankly speaking, When ever I have a question, I find the answer in one of your videos. All the best, Thamer

Excellent video. Merging multiple sheets is a challenge and this is what I needed

Your videos at Google sheets are awesome. Every novice can learn from it. Thanks

This is brilliant. I've been looking for a way to do this, and add in new rows dynamically, for several months. Thank you!

World's Best Teacher Best Quality Learn Easy and Understand I Love And Like All Video

Thank you! Clear and concise. Is it possible to also add a function to auto-sort the data by a specific column?

This was exactly what I was looking for. Thank you for taking the time to explain why you did what you did in the video. Amazing!!!

This is exactly what I needed to do and didn't know where to start. Thank you so much.

This was the video that turned me on to QUERY :) (glad to say I've come a long way since then!) Still watching every video you put out, so huge thanks !!!

Another question about sorting. Currently, it's sorting by the first worksheet (Info Literacy) then by the second sheet (Public Libraries)-- I would like to sort the combined worksheet by Column C, 'Due Date'... so that the dates on the third/combined worksheet (titled "Combined") are in ascending order. Where would I add the ORDER BY part? Any input appreciated, I have been messing around with this for a few hours now...UGH. Thank you! -- ​ =QUERY({'Info Literacy'!A2:G;'Public Libraries'!A2:G},"select * where Col1 is not null or Col2 is not null or Col3 is not null or Col4 is not null or Col5 is not null or Col6 is not null", 0)

Your videos have been SO helpful! They are the first place I go when I need to figure something out. Thank you! Thank you!

Thank you for this tutorial. Is there a way to do exactly as you describe, but the master knows when a new "tab" or sheet is added so I wouldn't have to change the query each time a sheet is added to the workbook? Thank you.

Watched this yesterday , the finest example. Thank you

This is amazing, I have a lazy way of using what you show in the first part on a hidden sheet, then filtering that with the filter function on another sheet...

Awesome! This is exactly what I need to bring data from different sheets together before exporting to Data Studio. Thank you so much!

Thank you! This video is so clear and it's making my life so much easier right now!

This was awesome. Thank you. Your tutorial was very easy to follow and understand.

thank you i have setters and now i made a 38 pages and it worked

This was incredibly helpful. Your explanation was perfect for my scenario. Thank you!!!

Now that I have a master of my data being fed from 3 different sheets, can I sort the data in the Master Sheet and not mess up the data in the sheets that are feeding the Master Sheet? Great Video by the way.

Fabulous tutorial, very well explained and helps me to accomplish just what I set out to do. Thank you!

This is great. Thanks. Is there a way in which I can add an unique sheet identifier in the master data to know where the data has been imported from to back track for eg during debugging? So in your example, an additional column which would have say "Partial Data 1", " Partial Data 2" OR "Partial Data 3" for the respective rows.

Dude you are AWESOME, even 5 years in future!!

Perfect solution for the question I had. Great job explaining! Thank you.

Really Helpful for me.... helped me with a task I have been pondering for quite a while. Well done series of tutorials.

Thanks man, i didnt't realize I was adding those blank cells... great video.

perfect, thanks! was wondering why the query wasn't working and adding the 'where' clause sorted that.

hi!! I tried this and it works pretty fine, I also followed your tutorial of doing this with a script because I have too many sheets to combine... However, I noticed query does not get all the text data from all different sheets. only the number values are showing.

This senior is a great teacher!

This was really awesome! I've learned something helped me solve a problem that I couldn't do on Google Sheets! I was surprised how easy this was!

8:24 Col4 already added before that, but the code still works so its good to know that its still work

I am trying to use query formula in google spread sheet to combined the multiple worksheet. Formula is working and Showing complete data also, but not showing full date in date column only. That is, it is visible from 1st to 12th but is not visible till later.

This video is so helpful! The only thing I'm needing help with in this is how to keep the format of the cell if there is a color filled in? When I completed this the cells that had a color filled in where cleared? Is there a way to do this?

Thanks man I was struggling with filter function for this .. appreciate it man😊

I like the teaching style. Showing us various solutions

the ZERO!!!!!!! Thanks - that was what was throwing me off and making the first bit of data collect several cells and combine.

Thank you man, needed this for my job and looks like your channel will really help me!

This was really really helpful! Just 1 query about it If in any column we have values with comma and we want as it is getting to master sheet. How do we do that? Because when I applied to my sheet, everything got copied except comma values.

Is possible to transfer automatically from Master tab to other partial tabs per selected data/name/brand/number/ect? For example in your case that you make partial tabs per brands from Master tab. So when you insert data in your Master tab, the data for Adidas goes to Adidas tab, data for Crocs goes to Crocs tab, ect.

How to auto sort the master tab data's using date column.. bcz both techniques get data's from all the tabs but inserting data in master tab inbetween.. I want the new data from any other tabs should be entered in the last row of the master tab... How to do it ?

Super duper helpful! Tho im having a problem pulling the data since I have 1 column that has hyphen or dash character and the query formula is dropping it. Do you have solutions for this?

This is really great! Thank you for teaching and sharing your knowledge. Your videos have really helped me a lot. I have 1 question related to this video- How can I import data if my sheets do not have exact same number of columns? Please guide! Thanks

A life saver man! Thanks a lot. This was useful for my Google Data Studio Report

Clear concise and covers a useful technique. Excellent video, thank you.

Thanks this helps 90% already, but after combine to master sheet, how do I arrange by dates ? And how the colour doesn't follow too.

Amazing man. That worked perfectly

This guy is the top G

What I would really love to see is a tutorial on comparing variables from two different sheets, and also querying another sheet using a cell in this sheet as a variable. I didn't find anything in this or the other tutorials which dealt with that. Also, how to concatenate different sheets with different column names and data sequences.

very effective demonstration! can you make tutorials for SQL queries too? I don't believe there is a single channel on KZfaq that's effectively teaching SQL.

Thank you. This would make our report automated. :)

Can you then put the Master sheet in date order and for it to automatically update in date order when additional rows are added to the other sheets?

Hi, It was very helpful. Is there any way to add a column with sheet Name Like "Partial Data 1", "Partial Data 2", ...

You are the best!

What is the difference between using query vs vstack to append date from different sheets?

this is great, thanks! but this doesn't transfer the formatting and design, e.g. cell color, drop-down options etc. Can anyone help me with that please?

Thank you so much for your explanations. They are spot on. I'm confused about Google Sheets inserting rows into a sheet that includes one or more array formula functions. In fact, I have a sheet where 999 rows were inserted just after row 987. I deleted the blank rows thinking I had fixed things, but any array formula functions with a row number of 988 or higher either don't work, or they have some error conditions. Thoughts?

How can we edit the query formula in such a way that in Col H , we get Partial Data1,Partial Data2 , Partial Data3 , corresponding to the rows from where it was imported.

What if the data types of columns are different in the 2 sheets that are combined into one? How do we tackle that?

don't understand why one of my column's data for 2nr thru 5th sheet no longer shows once I added the select filter. They shows up without the select but then I have all those blank lines

great tip! thanks for sharing what would be the "failure modes" on using the Sort Function instead of Query? ex: =sort( { range1 ; range2 } ,1,true ) tks man!

This is very helpful, thank you so much for this video. But may I ask how do you filter and sort by the latest date? Hope you would answer my query. Thank you again.

Cant seem to add a filter to all the compbined data. If I want to be able to sort everything by date, it doesn't seem to work.

Thank you. The Instruction was very helpful. I do have a question. Is it possible to maintain the formatting of the columns in the original tab in the new tab created with the array? For instance; font color, cell color, etc..

Thanks a lot for the video. It did help me!

This is a fantastic tutorial. Exactly what I wanted

Is there a way to pull this data from one document to another? I was hoping to find a way to pull the information from each of the tabs into a separate "Master Sheet" document instead of a separate tab.

Great tutorial! Thank you sooo much!!!

Help needed: It worked for me multiple times and now it makes a mistake I don"t understand: It doesn't put the data from sheets below each other - so the data from all column A to column A (not after sorting), but it puts them next to each other. I don't know why it works like this. Any idea?

Hi there, I'd like to know, If I create a "master" sheet along with a couple of others.. lets call them "data sheets 1 and 2"; and I make changes only to certain columns/rows on the "master"... For example. column D1 - D10 and want only those changes/edits made to D1-10 to reflect on data sheet 1 or 2. Is that possible? It's basically the opposite of what you're explaining here.

Help! followed your video and was a big help. Except I want to insert data into the master spreadsheet and have it transcribe onto the individual sheets it came from. Editing dates or values on the master. Any way someone knows how to do this?

For some reason the semicolon is not working; after I put in the same equation as the video, only the first tab of data shows up on my master tab. Any solutions??

I like to ask that whether I can enter data in Master sheet (on columns no included in the query command) for further Data . I have tried but whenever I enter any individual sheet data the data entered in master sheet get mixed up. Any suggestions please

very very nicely done and explained, you helped me out. thanks!

I have used the QUERY Formula to combine two sheets where one among them is response of Google form. When the google form response is edited, the entry of the respective found to be duplicate, i.e, one is older entry details and secound one is newly updated details. How can this is resolved?

thanks you so much, its very helpfull

Is it possible to do this while also only including rows that have a specific text?

Thanks Awesome Video!! How to sort it as per current date in master sheet?? Pls Help

I would like to combine data from multiple sheets, but where the data is in a vertical column on the individual sheets, but would be displayed in rows on the Master sheet. Is this doable in Google Sheets? Also, I would like the ability to do this where the source data is not necessarily in one source column, but is in select locations, with the source data selected from three separate columns (one cell per line), and would appear on the Master sheet all in one line.

Is this also working, if I have way more columns in the partial data, then in my master data, can i somehow perform an auto mapping only to the data i need in the master sheet? What would be the best way to achieve this? Thank you :)

Hi I just tried to do the same, all is perfect except,1 cell in first row, in column 3 is became empty?

Is there a way to filter the data by date for the master sheet? When I add a filter to the master sheet for date, it sorts by array instead as a whole sheet.

Hi, is there any way that when it is consolidate in the Master sheet, it automatically adds up SALES for a specific person so it will look like just one entry person? Given for example all other details are the same with the Region,State etc. Basically I want to see total sales per person in the Master sheet. Thank youuuu!

Hey, thanks for the tutorial but I have question. If I want to add custom columns on the master sheets to fill in some information about the sales rep. in example, is it possible if the data change my manual input to the columns also follow the move according to the sales rep. row? Thanks

Hi, how can I pull specific data from specific columns in a different sheet under different columns

Hi, how about a multiple importrange? (100 importrange all different file with same tab and different user) current method is 100 divider into for per 1 file for consolidation. Is there a much more efficient way of doing it?

Cannot combine data if the same column with different type to be merged from multiple sheets. For eg: Sheet 1: Marks Obtained field contains scores (integer data eg: 4, 5) and Sheet 2: Marks Obtained field contains scores but string data (eg: 4Y, 5Y). While combining, only shows integer data in Marks Obtained field. And string scores were shown blank. Name Marks Obtained Sheet 1 ABC 5 Sheet 1 XYZ 4 Sheet 2 ABC1 Sheet 2 XYZ1

Sorry didn´t watch enough of the video, you answered already.

Hi this is really interesting. What is I have a master list of order data that is updated automatically in real time with a new row at the end each time I receive an order. I then want this data to be automatically put into a sheet for that orders month (i.e. January 2021)? Is this possible?

Very Helpful, Thanks

is there any way to add newly entered data after the last edited row,column in master sheet I have two sheets A and B connected with two google forms, if someone enter the data in sheet A it should appear in master TAB after the last entered data but it appears in the middle where Sheet A's data ends and Sheet B's Data starts, Waiting for your response

Thansk alot.... The Tutorial is very Help full and you are a good trainer..... "Subscribed"....

How do you make the master in alphabet like? I want the list to mix in the masters like if I am doing characters sheets of their states? is there a way to filter that works I tried that and didn't work?