AWESOME Excel trick to combine data from files with DIFFERENT headers

Поделиться
HTML-код
  • Опубликовано: 6 сен 2024

Комментарии • 113

  • @alfredoluwatomi6899
    @alfredoluwatomi6899 Год назад +12

    Waoooo,this is awesome.I have been looking for something like this but all the videos I watched are just too way complex to understand.
    Thanks so much Chandoo for this simple straight forward approach,you are such a fantastic guy in excel.
    More power to your elbow.

    • @chandoo_
      @chandoo_  Год назад +1

      I am glad you enjoyed this, Alfread. My elbows are getting their own SIX PACK, thanks to all the file combining I've been doing. 🤣

    • @Unknown-ng8me
      @Unknown-ng8me Год назад

      @@chandoo_ Sir how to get job as a fresher in this field and from where we can get

    • @kganesh3886
      @kganesh3886 Год назад

      @@chandoo_ Error Data 😒😒=source{data){0}

    • @sehrishkiran1165
      @sehrishkiran1165 5 месяцев назад

      @@chandoo_

  • @ramnaslai2980
    @ramnaslai2980 День назад

    Great video.
    After watching 10 times, I have understood now.

  • @BenDieselBasicAutomotive
    @BenDieselBasicAutomotive Год назад +8

    Very complicated topic yet... explained in simplified way.... I was able to get it.... even if am just an average person and has no formal @ educational training in programming... I applied most of your excel techniques in my office work. My Boss was impressed with my presentation... Thank you very much.....

    • @chandoo_
      @chandoo_  Год назад +1

      Wow. so glad to hear this Ben. Kudos on acing that presentation. More power to you ⚡

  • @ExcelWithChris
    @ExcelWithChris Год назад +3

    Just a question. If the Receipt column was not in the Sample File, will it still come through? Did it not now come through because by chance we used the file with the Receipt column as the Sample? Or will all columns of all files pull through independent of which was the first sample file? I also see that if you combine, it used Column1, Column2 etc as headings. If I test on my side, it automatically picks up the top row of the files as headers and THAT is causing the problem as it used the first row automatically as the headers and therefore it will only import corresponding column names) What am I doing wrong? I went back and switched off the detect headers and thought that might help and that it would import all columns, but when I select the file with only two columns (and now Excel is NOT using the first row as headers) it still only combines the first two columns and still skips the Column 3 because it is empty in the sample file. I know you can solve this with code, but I suggest creating a dummy file with as part of the folder files with all the heading I might want to detect and use that is the first file in the process. Or how would I solve this?

    • @chandoo_
      @chandoo_  Год назад +3

      Good questions Chris. If you examine the "Transform sample" query, you will see the assumptions PQ made. If you have variable number of columns, it can be tricky to handle it, but possible. I suggest using the drill-down approach (data{0}) so that we are not exactly getting any columns but just the entire sheet.

  • @teoxengineer
    @teoxengineer Год назад +1

    Chandoo, it is so so good tutorial. I have sometimes trouble for this issue and I couln't solve it and it makes my brain confuse.
    Are there any more examples to combine different methods?

    • @chandoo_
      @chandoo_  Год назад

      Thanks Emre. See these other tutorials too.
      Combine all workbooks - ruclips.net/video/SGzegma9bdY/видео.html
      Combine all sheets - ruclips.net/video/k_ugshJ4wIw/видео.html

  • @abhishekupadhyay6938
    @abhishekupadhyay6938 Год назад

    Every time I watch your video I learn something new. Keep it up.

  • @arbazahmad7177
    @arbazahmad7177 Год назад

    Excellent and easy to understand👌.
    Thanks you Chandoo for this Mcode..

  • @sakeobulesu7130
    @sakeobulesu7130 Год назад

    Thanks for teaching us.. chandu sir

  • @yufanli6
    @yufanli6 3 месяца назад

    this is exactly what I am looking for ! Thank you so much !

  • @math-buddy1754
    @math-buddy1754 Год назад

    Hi, CHANDOO ji Very complicated topic Part-1 is done still have doubts about Part-2 and need more help.....

  • @bigt6928
    @bigt6928 Год назад +1

    Exactly what I want to learn! Thank You!!!

  • @williamthedataprof
    @williamthedataprof Год назад +1

    Good stuff, Chandoo.
    It's good to highlight though that the last solution heavily relies on the columns being in the same order, am I right? ...and therefore there's a chance to have mixed data if that is not the case

    • @chandoo_
      @chandoo_  Год назад +2

      Yes. You can't combine data unless something is fixed. So either column names stay same or columns are in same order. Otherwise, how would we know which column has what with certainty??

  • @janinemccown617
    @janinemccown617 Год назад +1

    love this video and great information I can use right away

    • @chandoo_
      @chandoo_  Год назад

      Glad it was helpful!

  • @johnborg5419
    @johnborg5419 Год назад

    Thanks Chandoo !!! This is a very common problem to many!! Thanks again!! :) :)

  • @BenDieselBasicAutomotive
    @BenDieselBasicAutomotive Год назад

    Wow!..... new learning for the day!.... Thanks again, Sir.... I'm an avid fan...

  • @alexrosen8762
    @alexrosen8762 Год назад

    Very useful tutorial. Thanks Chandoo

  • @abhishekchahar7917
    @abhishekchahar7917 Год назад +1

    Hey chandoo. Good video but in my Excel in power query option I'm not having combine option. So how to combine all files as you have done it in video.. plzz give me a alternative of this..

  • @RAJESHS-df8io
    @RAJESHS-df8io Год назад

    Chandoo 👌 Excel dooo

  • @thstudies
    @thstudies Год назад

    Very well explained SIR

  • @actedorganization9857
    @actedorganization9857 Год назад

    Hi , thanks for the outsanding session. I am working as information management, but i was dealing large of data sometimes combained 3 sheets with heads rowa, the issue that i cannot solved is when uncheck the null valuse , the recods from master sheet(combained sheet) will disapear or if uncheck null value other data will be null defently

  • @mdshahbaz4766
    @mdshahbaz4766 Год назад +1

    Sir plz reply deos data analyst jobs will disappear in feature in next 4 to 5year whatever the role data analyst has right now that r going to done by the data engineer or data scientists in feature????

  • @calbiju
    @calbiju Год назад

    Hi Chandoo, it is a wonderful video. Am trying to combine files with multiple sheets with different names and different column names. It’s way too complicated.

  • @kirandaund6295
    @kirandaund6295 Год назад

    Hi can u help me with how to convert file in CSV format from binary file or any other file format with just any shortcut or any other 1 click or add ins I need to do it daily on save as then drop down csv format

  • @vv9730
    @vv9730 Месяц назад

    nice,but how to approach when only one of the multiple files have headers,rest have data in same column order,but dont have headers..!!!

  • @shubhambarolia2176
    @shubhambarolia2176 Год назад

    I am facing both problems simultaneously and How to achieve this in multiple sheets

  • @karimallahwala7022
    @karimallahwala7022 Год назад

    Thank very much for sharing that great knowledge

  • @niggszo
    @niggszo Год назад

    Chandoo is "Awesome".. Thanks man!

  • @BOOBOY19
    @BOOBOY19 Год назад

    Hi Chandoo i'm one of your sons friends Nish. and I was in nakshu's class last year, you have great content and tutorials!

    • @chandoo_
      @chandoo_  Год назад

      Hey, thanks PJ_ 😎

  • @umersohail1951
    @umersohail1951 11 месяцев назад

    nicely explained Sir, although I am facing an Error: The column 'Column1' of the table wasn't found. can you pls tell me how to troubleshoot this problem?

  • @divinelyblessed2497
    @divinelyblessed2497 Год назад

    How can non IT person can learn automation.. Any videos on automation.. Pls advise .. Thanks

  • @successmantra4525
    @successmantra4525 Год назад

    It awesome, today I was looking for this topic and i got yours as notification. Im happy in learning this.
    I would like to get one more video on the same topic. Where we have multiple workbook with multiple sheets but all work book has same format and even the sheet name. How to consolidate the workbook as per sheets.

  • @FirestormT
    @FirestormT Год назад

    As always awesome :) Thank you Chandoo

  • @phoenix_ever
    @phoenix_ever Год назад

    Thanks a lot Chandoo, great content really helpful, could you also guide how do we handle if the data is huge 11L+ rows after merging the files. Thanks in advance

  • @sadiaehsan787
    @sadiaehsan787 Год назад

    Please make video on Small, ROW Combined in excel ...for filtering data

  • @user-oe1mb5is8d
    @user-oe1mb5is8d 2 месяца назад

    What if you need to combine two files that have partial information about each customer. How do you combine them but create a single row with all of the customer data listed? For example, I have two sheets, one where the customers' name and total ARR is listed and another sheet with the customers' name and size of them. I want a single sheet with all of these columns for the customer on a single row. Would that be possible? 🤯

  • @thealexanders7828
    @thealexanders7828 Год назад

    Great videos as usual!

  • @madmaagic
    @madmaagic Год назад

    could you make some tutorials on how to analyze stock, share, and companies on excel?

  • @mandeepkaur-xl1zw
    @mandeepkaur-xl1zw Год назад

    Hi Chandoo ur channels helps me a lot. I just wanted to know do we need any kind of certification to become a data analyst or we can become a good analyst just watching ur videos and implement it on the practical data sets. I hv done my BCA and MCA. Hope for an really response from u.

  • @dipanjandash4485
    @dipanjandash4485 Год назад

    Excellent

  • @automation254
    @automation254 Год назад

    Nice time saver.

  • @anandl2003
    @anandl2003 Год назад

    Thanks chandoo

  • @pidzyn
    @pidzyn Год назад

    Awesome!. Thanks a lot for that!

  • @sandorszilagyi5929
    @sandorszilagyi5929 Год назад

    Extremely helpful, thank you !

  • @taizoondean689
    @taizoondean689 Год назад

    Thanks Sir...🙂

  • @nazarkamal8831
    @nazarkamal8831 9 месяцев назад

    Great 👍👍

  • @Sahr111-Mus
    @Sahr111-Mus Год назад

    So so helpful

  • @cp2345
    @cp2345 Год назад

    Thank you.

  • @igordragicevic0606
    @igordragicevic0606 Год назад

    I have a question. I have 100 worksheets and i want to extract B2 cell from every worksheet in new worksheet, in new table. Is there faster way than manually linking cell from each sheet in my new table? Ty

    • @chandoo_
      @chandoo_  Год назад

      You can use Power Query to do this. See my video on combining data from multiple sheets and customize it to get only B2. ruclips.net/video/k_ugshJ4wIw/видео.html

  • @pepper_lab
    @pepper_lab Год назад

    Whoaaaaa great 👍👍👍👍

  • @ICTNuggets
    @ICTNuggets Год назад

    Excellent!

  • @zaighamuddinfarooqui1705
    @zaighamuddinfarooqui1705 Год назад

    Awesome💐

  • @mohammadnaved7079
    @mohammadnaved7079 3 месяца назад

    great bro

  • @martinangelraj3415
    @martinangelraj3415 Год назад

    Excellent buddy😍

  • @radiali482
    @radiali482 Год назад

    awesome , thanks

  • @shyamadasgupta9423
    @shyamadasgupta9423 Месяц назад

    Thank you! Can I use the List transform method to combine files with not only different headers but different number of columns with no unique column/column header between them. My clinical trial databases all make headings and number of columns as per their own company.
    No one column header or internal info matches the other databases (i.e., the trial identifier called NCT ID may be a separate column in one database, but in another database, it maybe in a column called 'Identifier' with each cell having the ID & other text/number bunched in).
    I just need to throw the duplicate trials and keep unique records as one database may be more up-to-date and have listed more trials and the other might be missing some trials.
    NOTE: All clinical trials are idetifiable by world-centralized NCT IDs. That's how I know there are duplicates between databases.

    • @chandoo_
      @chandoo_  Месяц назад

      You can try. But you will need a robust method to identify the correct columns. Otherwise, you will end up with a jumbled mess.

    • @shyamadasgupta9423
      @shyamadasgupta9423 Месяц назад

      @@chandoo_ Thank you! What i will do is clean up the data first by splitting the 'identifier' column into NCT ID and other unnecessary extra text/numbers. That way I will have the NCT ID as a common column among all the databases.

  • @zubairsiddique5566
    @zubairsiddique5566 Год назад

    Hi Chandoo
    When i tried to combine file,still got error. How can i fix it

  • @maheshchagi6349
    @maheshchagi6349 Год назад

    Very useful video,
    I have a doubt if any of the row is having a blank cell, for that blank cell, can we fil it with color in PQ, and it should be applied for all the exell once we consolidate using PQ.... any inputs or suggestions

    • @chandoo_
      @chandoo_  Год назад

      PQ cannot colour cells for you. But you can do this with Conditional Formatting in Excel.
      Select the data, go to Home > Conditional Formatting
      Click on "Hightlight cell rules" and then select "More rules".
      Change the "Format only cells with" option to Blanks and apply the color you want.

  • @mdliyaquathusain1239
    @mdliyaquathusain1239 Год назад

    excel 2016 has differentinterface. i am unable to perform this task

  • @operator8194
    @operator8194 Год назад

    A+

  • @markan6076
    @markan6076 Год назад

    Great one, Chandoo! I seem to have one more problem on top of "Columns not in same order" - some columns are missing in some source files. How do I "Transform Sample Flie" in order to avoid adding the 3 missing columns into all faulty tables in multiple files?

    • @chandoo_
      @chandoo_  Год назад

      You are welcome Markan. You can add a step in the transform sample to keep only the columns you want.

  • @Dan_De_Man
    @Dan_De_Man Год назад

    Works up to removing columns. I'm working on first file in transform sample file. If I remove column 1 and 2, the different order query has an expression error. The column column1 of the table wasn't Found. Ps in the zipped files July is called july-2022-z.xlsx none of the others have the z

    • @chandoo_
      @chandoo_  Год назад

      You are right. I did not notice the inconsistency in file names before. Must have been a typo. Nevertheless the technique should work fine.
      For different order, you can't delete columns in "transform sample". Instead, do it after you combine all data.

    • @Dan_De_Man
      @Dan_De_Man Год назад +1

      When I deleted changetype in different order query it seemed to remove the error

  • @EthanEminence
    @EthanEminence 8 месяцев назад

    Expression.Error: The column 'Column1' of the table wasn't found.
    Details:
    Column1

  • @pallabihazarika6101
    @pallabihazarika6101 21 час назад

    Get data option not available what to do

  • @republicmanrajesh1874
    @republicmanrajesh1874 Год назад

    PLSQL video on this topic please sir 🙏🙏🙏

  • @maverick_anoop
    @maverick_anoop 6 месяцев назад

    What if my header are not available in first row (horizontally) and are in the first colomn (vertically)?

    • @chandoo_
      @chandoo_  6 месяцев назад

      You can use table transpose feature (in the transform ribbon) to turn the table and do the rest.

  • @chintan219
    @chintan219 Год назад

    Awesome. I have a small question for you. I extract the data in PQ then afterwards I need one column to add I. That extracted data. So how I can add that previously removed column in PQ? Thanks.

    • @chandoo_
      @chandoo_  Год назад

      You are welcome Chintan.
      You can select the step where you removed the column, edit the step (using gear icon ⚙) and add it back.

    • @chintan219
      @chintan219 Год назад

      @@chandoo_ it worked. But I deleted many column so it take time to arranged but it worked. Also find new error. Which data I extracted the date column every time I refresh the data sheet the date changes everytime 🤦. How come I fix this? Thanks.

    • @chandoo_
      @chandoo_  Год назад

      Not sure I understand. What do you mean ?

    • @chintan219
      @chintan219 Год назад

      @@chandoo_ I extracted data using PQ every time I refresh the data, The Date column format changes everytime. In source file the Date column formate I already set as Dates. How can I fix this problem? Thanks.

    • @chandoo_
      @chandoo_  Год назад

      Could be a bug. When I set format to "Date" in form Power Query, Excel shows it as date for me. In any case, the format is for visual use only. The dates are stored as numbers internally anyway. If you do use the table for anything else (pivot, formulas etc.) you will need to format again.

  • @mentertainment1213
    @mentertainment1213 Год назад

    How to remove unwanted spaces from multiple columns in power querry

  • @Hurmali1
    @Hurmali1 Год назад

    Hi Chandoo, thanks for this. This is great, looks a bit simple. But I keep having a problem when I enter the formula =source[Data]{0}. My Data would just get (!) sign. I wonder what I might have done wrong. I tried to follow you again and again, slowed down the motion, but still, it gets the same sign. Pls help, salam dari Indonesia.

    • @chandoo_
      @chandoo_  Год назад

      Hmm... Can you check what you get if you write just source[Data]?
      Please note that Power Query is case sensitive. So the step name should be exactly as it appears. I think it would Source[Data]

    • @Hurmali1
      @Hurmali1 Год назад

      @@chandoo_ I take care of all those sensitive stuff, but still with the same result. the s in 'Source' is also in capital shape. Also the technique in the first half of the video does not work for me.
      Or, the original files must be first shaped in certain ways to get better processed by this technique? Terimakasih.

    • @Hurmali1
      @Hurmali1 Год назад

      oh, the next result after Source[Data] is List

    • @chandoo_
      @chandoo_  Год назад

      So the list works. What is the first element of the list? You can just select the first item and double click to drill down. This shows the value. If you are getting error at this point, it means, there is something wrong with your list's first item.

    • @Hurmali1
      @Hurmali1 Год назад

      @@chandoo_ thanks Chandoo. Yes correct. I click it, all the content of Transform Sample File appears, and the data turns getting the ! mark.

  • @wasimshaikh5794
    @wasimshaikh5794 Год назад

    Not able to combine huge data with same headers through power query. Each spread sheet has 9lac rows of data and total is 7 spreadsheet. Any suggestions on this?

    • @chandoo_
      @chandoo_  Год назад

      You "can" combine such large volumes of data, but you can't push it back to Excel as it has a limit of 1mn rows. You can use the datamodel to store the data though. See this video for an idea - ruclips.net/video/5u7bpysO3FQ/видео.html

  • @justmemyselfandi5532
    @justmemyselfandi5532 Год назад

    That is all nice, but where do you find complete list of commands available in Power Query and that it is not in "help file". Once wen I know all commands available and what they do I have no problem to combine them to do what I need to do. Do you have any tips on that matter?

    • @chandoo_
      @chandoo_  Год назад

      You can check the official documentation. - learn.microsoft.com/en-us/powerquery-m/

    • @justmemyselfandi5532
      @justmemyselfandi5532 Год назад

      @@chandoo_ thx I guess I should use Bing search to get this one, since goole gives everything but not this 😉

  • @revmure452
    @revmure452 Год назад

    How to combine data from multiple excel with multiple sheets

  • @Denjixerwin
    @Denjixerwin Год назад

    DO YOU HAVE VIDEOS FROM BASICS TO ADVANCED ..?

  • @mohanbabuj2555
    @mohanbabuj2555 Год назад

    Hi sir, can u give solution for my doubt. It collection no. Of days for each invoice. Invoice 1 rs. 10000 DT. 01.01.23, invoice 2 rs. 5000 dt.20.01.23 invoice 3. Rs. 3000 dt. 01.02.23 invoice no. 4 rs. 8000 dt.09.02.23 and collection 1 rs. 4000 dt. 10.01.23, collection 2 rs. 10000 dt. 29.01.23, collection 3 rs. 4000 st. 06.02.23 and collection 4 rs. 10000 st. 18.02.23. Can u pl invoice wise no. Of days from collection date. In excel formula.. Pl....

  • @mayuronce
    @mayuronce 11 месяцев назад

    Make videos in Telugu also

  • @darthmichau878
    @darthmichau878 Год назад

    😁

  • @nt4231
    @nt4231 Год назад

    Hi Chandu, I am interested in taking class , what’s the procedure, I am in 🇺🇸, have a good knowledge in excel, still like to learn from beginning….
    Please contact me or how do I get connected with your….
    Thanks 😊

  • @justinwduff
    @justinwduff 11 месяцев назад

    Excellent video, thank you!

  • @ismaelkourouma5558
    @ismaelkourouma5558 Год назад

    Excellent