This is great, much more effective than the consolidate function, thank you! Do you know how to do it if the headers were not in the first row, but the second or nth row instead?
I can't seem to import the file in the same open workbook. Why? I get "Unable to connect. We encountered an error while trying to import. The process cannot access file C:\xxxx\xxxx.xlsx' because it is being used by another process".
Hey so I fixed it for myself, if your file is stored in OneDrive it won't work, try storing your workbook locally on your computer and see if that works.
Thanks a lot for this simply and clearly described complex action! For me it is very useful and I will apply it in my work. Keep making such useful videos. Thank you!!!!
Used this site for all my Google dev work. Now starting on Excel. Awesome tutorials. Is there a similar way of getting data where all the files are in OneDrive? I cannot find one.
Great and very well done. I have a Question about what you just said about the LINK. What IF I do All this work and send it to my supervisor? He will download and open on his computer, it will loose the connection?
If you have those files on a shared drive and both of you have access ot it then it should be fine, otherwise he'll be able to view combined data but he won't be able to refresh. I have other videos on this where I use a connection to the current active workbook, so it wouldn't have a link to worry about.
what do you do when replacing "null" with "true" does not pick up the column headers? I have tried using other options such as promote header, value, list all true, etc. Please help, please help
For some reason, when I'm doing this, I see all the data (just like you're showing us)...but when I do "Close and Load", it creates a new worksheet, but it has no data in it. And over on the righthand-side of Excel, it has a section "Queries and Connections" and my named Query is there, but it says under it "Connection only" instead of saying the number of rows like yours does. I'm thinking that maybe I need to start from scratch again & see if I missed an important step or something. Obviously, it's connecting well because I see all of my data from my two worksheets all consolidated nicely. But it's just not putting it into the new sheet it creates. :( If you or anybody else has any tips, I'd welcome them. :)
Can you do this if you have nested headers i.e. two columns merged with the cell containing the word "2020", under which there are two cells with the titles "January" and "February". But then in the next column you have another two columns merged for "2019", under which there are two cells again with the titles "January" and "February"?
If you have idea to make those macros from which we can get our desired header from our different process sheets eg: raw data consists of 3-4 headers lets say name company salary nd another sheets having different many datas eg data 1 having more then 10 heading but that includes that 3-4 heading in it , so we just need to catch that specific header details below , but the thing is whenever we will update that sheet with new data automatically data should get fetched into out desired header macro sheet. Does it make sense to uh?
I'm curious. Can I use a master sheet with data and link that data to go into multiple sheets? I run a 24 team baseball league and use excel for all data. Each team has a tab for it's players and salary. I wanted to use a master sheet with all the players and the players salary and link that to all the 24 team tabs. So if a trade occurs I could just switch the teams in the master sheet and it will then automatically swap those players in the individual team tabs
Hello. Let me know if there is a way to do this. I would like to program a range of cells in such a way that when I write a number in a cell, that is seemingly empty, Google Sheets will use that number and execute it in a formula that will then display the result in the same cell.
Hellol! This worked but when I try to sort by date it keeps the data grouped within whichever tab it came from. Is there a way to combine the date across multiple tabs and then sort all of that data by a variable where it will all mix together?
Wouldn't matter, so long as total count is below 1 million. If you have more than 1 mil, then I'm afraid you'll have to use something other than Excel.
300,000+ rows? in Excel? be brave and try R or python + pandas :-) easier than you think for data analysis, you can keep Excel for data entry SqLite could be your best friend too they are all free BTW
Hi, Do you have some video that can help me with this?, i need to automatize the randomize range for a row (each day). I want to make this ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9RMww80Da4w.html , but automatically and not manually. I know i must be active a trigger on script option sheets (to running each day or hour), but i'm not sure if i need to write a script. I don't have any code reference neither (i'm not developer). The sheet data will be show on a website, for that reason i want to randomize it. (sorry by my english, i`m spanish speaker). Regards! Felipe from Chile.
unnecessary talking and content, first of all try to show in a simple way how to merge the contents of different tabs with same column headers and then go to complex part of "Begins with" and stuff
maybe helpful if you want to learn excel in total. not helpful if you just want to find out how combining sheets work because I dont have the time to watch a one hour video until the significant 2 minutes show up. condense and focus in your videos. I personally didnt need to see all options and corners but rather how to get to the point.