thank you so much for your instructional video. After updating the tab name to be consistent across different workbooks, the consolidation worked! massive thanks to you!
Thank you for this video! After figuring out how to hide rows, I was able to (finally) import data from 5 worksheets and have a functioning query! 🙌🙌🙌🙌
thank you hugely for this video. i've been trying to find a solution for this for 2 years. consolidate, add ons, etc. did not work for the data i have. this works just fine (with a few modifications). thank you very much!
Man found this to late as last week I had to combine 10 workbooks with 30 sheets each. So had to rewrite my older macros that combined the sheets and added a loop for the workbooks in a folder. Which took me a lot longer then using a combination of this method.
Really helpful thanks! I have a folder with a lot of file and every day i have new files, can i load only the new files that i never loaded knowing that old file will not change and doesn't need to be refreshed every time i load a new file 😊 ?
Hello, Can we not consolidate the data in the same Excel workbook? And the consolidated sheet dynamically updates when data is added / changed to other sheets
I have 40 worksheets I'm trying to combine. When I get to the step of expanding data the result is a lot of "null" cells that should have data in them. Any thoughts?
Hiya, can you help me. I have a sheet with 20,000+ items listed. Lets call it the master sheet. From the master sheet I have saved a copy to work on different series within it and used filters to only work on what I need. I need to merge the second sheet into the master and it needs to slot back into its original order. Will this method work? Not sure if I have explained this very well. TIA
I was trying to combine 10 files each with 5 sheets, my target is to get the info onto one file on one sheet BUT the table must be in the order of each file. FAILED !!!! Any advice, please... 🙏
trying to combine multiple worksheets but I dont have a get data button, using excel 2016. My only options are Get from Access, From web, From text, SQL server, etc etc but nothing that lets me choose from a folder.
I have a query, but the source data is spread across multiple tabs and multiple worksheets and the tabs names can not be changes is there something i can do?
Please make a video on this. I have a sheet which contains data in rows and columns. And there are 4 blank rows after every data row and I want to fill the 1st blank row with the data of sheet 2 and 2nd row with sheet 3 and 3rd row with sheet 4 and 4th row with sheet 5 please make a video it's getting very difficult
I am trying to achieve the same but the problem is that my source file also is generated from a system and thus source file have random number of worksheets.
How to create a reference to the same cell range on multiple worksheets? Not everything in the spreadsheet, but only certain cells data into one. I need to create a list from hundreds of quotes sent to clients. I only need to pull their info and quote number to one list in excel in order to keep track of the quotes sent out. No one bothered to keep track before I started the job and I would like to implement this for myself.
Hi Jamie. I am having the problem with asking the correct question and I’m trying to reverse engineer stuff. Not working. I am so frustrated that I think because I’m not “subscribed” to Excel that MS has my Excel omitting or retarding its abilities. Or I’m stupid which I’m not. I have 200 worksheets filled out as “Form Sheets” and I’d like to make a seperated Sheet and pull the forms from all 200 in a common column so I can compare each sheets items. I miss dBase. That’s what I want to do and just can’t figure out what they changed what I want. Every query is like trying to combined the sheets, if I use Concat then I have F2+F9 each cell (4000)… I may as well type it at that rate. Macro I did went stupid half the time (where I get suspicious) It’s like taking a dBase form file and turning it back into data file I feel like I should’ve done it as data setup now but it’s about 8 pages per sheet and I’m trying to save my keystrokes tho I’m getting close to where I should’ve stopped. I didn’t get away from Excel for that long that the changes are stunting me…
Although helpful, this video just goes a bit too fast and I think covers quite a lot in a short time. I got lost at the "Applied Steps" stage in Power Query. If you simply want to merge lots of worksheets into one, without changing any columns etc etc, it would be really helpful, and possibly a clearer structure, if the tutorial simply explained that, before moving onto more complex things, such as performing the same operation but removing columns, etc.
The subtitles appearing at the bottom of the video tutorial get in the way of the actions being performed and renders the video useless. Please remove. Thanks!
Subtitles are a TH-cam feature which means you'll have to hover your mouse pointer near the bottom right 1/3 of the youtube display . You should see an icon that looks like a sprocket ( left click and) that opens the settings menu. you can turn off the computer generated caption/subtitles with additional left mouse clicks .
thank you so much for your instructional video. After updating the tab name to be consistent across different workbooks, the consolidation worked! massive thanks to you!
I had to do some slight of hand to get my particular use case to work, but your instructions helped me down the path. Thanks!
Glad it helped!
Super helpful! I had to merge 52 weeks of csv files (1 per week), so this helped massively. Thank you.
This is a wonderful tutorial. I was working on a project for combining data manually, which was a long and tedious process.
This is so much more simple than the Pivot tables I have been using. Thank you!
Thank you for this video! After figuring out how to hide rows, I was able to (finally) import data from 5 worksheets and have a functioning query! 🙌🙌🙌🙌
Amazing! Just combined 250k lines that were initially spread across 28 tabs. Great help, thanks very much.
Thank you for your clear and quick instruction. So nice to not have to listed to a ton of jibberish, first. Thanks again!
Brilliant 👏
thank you hugely for this video. i've been trying to find a solution for this for 2 years. consolidate, add ons, etc. did not work for the data i have. this works just fine (with a few modifications). thank you very much!
Man found this to late as last week I had to combine 10 workbooks with 30 sheets each. So had to rewrite my older macros that combined the sheets and added a loop for the workbooks in a folder. Which took me a lot longer then using a combination of this method.
great video!! This is a super useful tool!
Thank you . I enjoyed your class.
Woooo thank you so much!!! It is almost as Power BI. I'm. going to $$$ for that.
Very helpful and elaborate! Thank you for teaching and saving my time! :)
It works! Saved a lot of time. Thank you so much!!!
Thank you so much for the straightforward and clear tutorial!
great thanks for sharing! Very useful!
Really helpful thanks!
I have a folder with a lot of file and every day i have new files, can i load only the new files that i never loaded knowing that old file will not change and doesn't need to be refreshed every time i load a new file 😊 ?
Thanks a lot. It’s really really helpful! ❤
Hello,
Can we not consolidate the data in the same Excel workbook? And the consolidated sheet dynamically updates when data is added / changed to other sheets
Thanks for the wonderful guide! It works!
Thanks for the clear demo and explanation. Very helpful 🙏🙏🙏
wouah! thank you!!!
Thanks for the valuable video, done subscribe. How could we do if the data contains pictures in product, how PQ handle data contains picture
Great and simple explanation
very good explanation
super helpful
Thanks 🙏
Is there a way to add additional data to certain fields based on another excel file?
Literally sitting at my desk in shock....Combined over 20k lines. I could cry rn
thanks a lot sir.
I have 40 worksheets I'm trying to combine. When I get to the step of expanding data the result is a lot of "null" cells that should have data in them. Any thoughts?
Use filter
Hiya, can you help me. I have a sheet with 20,000+ items listed. Lets call it the master sheet. From the master sheet I have saved a copy to work on different series within it and used filters to only work on what I need. I need to merge the second sheet into the master and it needs to slot back into its original order. Will this method work? Not sure if I have explained this very well. TIA
Adorable ❤
I added some columns to the source files in order to get additional detail, how do I get these columns to carry over to the power query?
Thank you
you gonna save my day :D
I tried to do this following the video guide but I keep getting error message via Query "column data of the table wasn't found"
Thanks!
Welcome!🙏
Thanks Jamie.
You’re welcome!
I was trying to combine 10 files each with 5 sheets, my target is to get the info onto one file on one sheet BUT the table must be in the order of each file. FAILED !!!! Any advice, please... 🙏
Many thanks
trying to combine multiple worksheets but I dont have a get data button, using excel 2016. My only options are Get from Access, From web, From text, SQL server, etc etc but nothing that lets me choose from a folder.
It's new query button for the required data button
Will formulas come over or will that create an error?
This query seems to be rounding numbers. Any idea how to stop the rounding?
possible to do this in excel 2016?
Is there a way to do this, but have is different colors? E.g. West ones are green; east ones are blue; south ones are red
I have a query, but the source data is spread across multiple tabs and multiple worksheets and the tabs names can not be changes is there something i can do?
Please make a video on this. I have a sheet which contains data in rows and columns. And there are 4 blank rows after every data row and I want to fill the 1st blank row with the data of sheet 2 and 2nd row with sheet 3 and 3rd row with sheet 4 and 4th row with sheet 5 please make a video it's getting very difficult
I am trying to achieve the same but the problem is that my source file also is generated from a system and thus source file have random number of worksheets.
How to create a reference to the same cell range on multiple worksheets? Not everything in the spreadsheet, but only certain cells data into one.
I need to create a list from hundreds of quotes sent to clients. I only need to pull their info and quote number to one list in excel in order to keep track of the quotes sent out. No one bothered to keep track before I started the job and I would like to implement this for myself.
how do you do this in the same work book?
Can someone show how to do this on a Mac? It does not match.
Hi Jamie. I am having the problem with asking the correct question and I’m trying to reverse engineer stuff. Not working.
I am so frustrated that I think because I’m not “subscribed” to Excel that MS has my Excel omitting or retarding its abilities. Or I’m stupid which I’m not.
I have 200 worksheets filled out as “Form Sheets” and I’d like to make a seperated Sheet and pull the forms from all 200 in a common column so I can compare each sheets items.
I miss dBase. That’s what I want to do and just can’t figure out what they changed what I want.
Every query is like trying to combined the sheets, if I use Concat then I have F2+F9 each cell (4000)… I may as well type it at that rate.
Macro I did went stupid half the time (where I get suspicious)
It’s like taking a dBase form file and turning it back into data file
I feel like I should’ve done it as data setup now but it’s about 8 pages per sheet and I’m trying to save my keystrokes tho I’m getting close to where I should’ve stopped.
I didn’t get away from Excel for that long that the changes are stunting me…
Can I use vlook up formula
I do not have commands Get data and From file.
What if there is an error?
Although helpful, this video just goes a bit too fast and I think covers quite a lot in a short time. I got lost at the "Applied Steps" stage in Power Query. If you simply want to merge lots of worksheets into one, without changing any columns etc etc, it would be really helpful, and possibly a clearer structure, if the tutorial simply explained that, before moving onto more complex things, such as performing the same operation but removing columns, etc.
Note that this doesn't work on MacOS version of Excel
The subtitles appearing at the bottom of the video tutorial get in the way of the actions being performed and renders the video useless. Please remove. Thanks!
You’re able to turn them on or off.
Subtitles are a TH-cam feature which means you'll have to hover your mouse pointer near the bottom right 1/3 of the youtube display . You should see an icon that looks like a sprocket
( left click and) that opens the settings menu. you can turn off the computer generated caption/subtitles with additional left mouse clicks .