I’ve been using excel for over 20 years now, more than half in professional life. I’ve been trying to automate stuff and decided on pursuing python for that reason. Thank you for sharing your experience and wisdom with us.
Just started to learn python on my free time. As an accountant your channel is very helpful since you give examples of how I can use python to analyze data. Keep up the good work
Been searching the world wide web for 4 hours on how to do this, and this is the only video that correct depicts what I needed. Thank you so fucking much!
you deserve more views i believe that this is the future im from tijuana and all the time is consumed by making reports documents on excel thank you sir
Dude I don't usually comment on google videos however I feel inclined to tell you how useful your content is. I've watched some of your stuff and as someone new to Python that's transitioning from excel your help has been immeasurable. Thanks and keep it coming!
Thanks! As a python newbie and advanced Excel user, I struggle to learn Python as I'm very stuck to excel logic. Would be great to have a tutorial that shows how to do most of Excel data wrangling but better and quicker with python! Thanks!
Dude, you're just as awesome! The things you have shared are invaluable to people like me who are just beginning to learn Python. By far, your tutorial is best out there with all the actual real life applications. Thank you
This is a huge help! Excel is so processing heavy that python makes things so much easier. Really appreciate your videos and please keep up the good work.
Derrick, this is really helpful, it's my daily work job to find out such cases, till now I was using index match. But now I will do with the method u showed here. Thanks.
Hi Derrick Your explanation style and codes are simple and coo😎😎l gone through many videos on RUclips but none were practical and simple thanks bro keep it !!!👍👍👍 Have become your fan by just one vedio will explore all your content thanks again 🎉🎉🎉
thanks for this, widens the solutions range... a faster formula is index+lookup, to eliminate human errors we can use macros. I know xlsx can't have macros but we can quickly write one in the personal macros and use it on any tybe of wb
Thanks. Very informative, quick and easy tutorial 👌. I’ve been spending lots of times trying to figure out how to do it in python until I came across your channel.
Hi Derrick, the common assumption which everyone makes is that, they assume we always work on a new excel file and the merged data we put it in last columns.. But i have an old excel file and from which i need information from a specific column. Using the vlookup its easy but python merge (the one you described) i get the entire data from the old file.. here are the steps 1. two files : old.xlsx which has 10 columns and column 3 is having sales data & the new.xlsx where i need bring in the last month sales in a newly inserted column. 2.need only only column data from old.xlsx to the new file. lets say, the location key is common and last month sales not sure if the above describes the requirement well.. not sure how inner join, outerjoin helps but dont understand them
@Derrik Not sure why you made the distinction between a key column and a target column. Is that important? Wouldn't df_info[['ID', 'IDs']] work the same in pd.merge? The essential thing here is what we're joining on wihch the column 'IDs'
Hi Derrik, This was a nice video. Could you please make a video on how to compare two excel files and highlight the differences in a third excel file? Could you please help. :)
Hi Derrick, How can I merge 2 dataframes with duplication in keys in both the dataframes and I need a FIFO allocation of 2nd df values to 1st df value. Currently I use nested for loops, flags and it is very slow.
Great video. Just to confirm my thinking. Are you saying that by merging two tables we are achieving what vlookup would do. which in this case there was two codes that had a corresponding value in the second table. And so merging the two tables means that only values are going to be filled in that column because the code appears in both tables?
How can a xlsb workbook can be read. Don't we need any libraries to be installed? Do I understand correctly that key column in the info file was not at left side which is requirement of excel vloomup
How about when I need to vlookup from two tabs? In excel I use something like IFERROR(xlookup in TAB 1, xlookup in TAB 2). How to do that? Should I place IF statement to look in other table if the output is NA?
I created a program to copy an Excel spreadsheet line into a older Microsoft Excel visual basic program . I wonder if the python program can be used to clean up the database. Is there a better way to record data.
Hello Derrick, I had a question regarding using openpyxl and working with excel sheets using python. I want to link one cell from a sheet to another sheet within the excel file, so that when I click that cell, it takes me to a particular sheet in the file. I have been struggling to find a concrete solution but in vain. Is there any way in python to do the above task? Please let me know if you have any thoughts on this. It would help me greatly. Thank you.
Hi Derrick, I want to use vlookup function in existing excel sheet. Don't want to create any new data frame every time. Please help me on this with simple example. Awaiting your revert.
Hi bro. Could you please make a video where you explain how to select values from different rows and colums to create a new column. For instance, in the first row select the column 3 in the second row column 5 and so on. I would really appreciate it!
I need to know whether this can be automated or not: I get one raw data details on excel sheet on mail with attachment, this attached excel sheet gets automatically downloaded to certain location and then macro runs on that excel file as per schedule and then that excel sheet is send to several email addresses as per schedule without human intervention? What is required to do this automation?
I got a case here. I have 4 excel reports with same kind of data but with different header (column) names in each report and also one of the report has 29 tabs with similar data but for different accounts. I need to append all the 29 tabs data + data from 3 other reports into one single excel report and I would only need few common columns from all the 4 excel reports. The tab name remains same throughout and all 4 reports gets updated once a week with their respective file names suffixed with latest date. How do we go about this using python? Appreciate your help. I have used power-query too but curious about using python.
I am coding in python and pandas since 3 days. This one solved my problem. But simply cant believe that this is the best practise solution? Pandas must have a certain command for this simply task or am I wrong? Anyway, thx a lot.
Hi Derrick, thanks for the video, it is really practical & easy. I got another query on excels which i have a few thounds of mobile number to validate for diff countries. May I know what is the best solution to calculate the invalid mobile (not in country code + mobile format)? Thanks!
Hey, nice work! Thanks a lot for your effort! I‘m completely new to python. I wonder why we don‘t have to insert a full path to the excel files here or do I have to and you just skipped the info to keep the example easier?
Plz guide whether vlookup method u shared on your video for python has same limitations like in excel i.e. it returns the the first record it finds or it can go to second record if first is already found earlier in the result column.
Hi Derrick, thank you for sharing your hard skills ! I have a problem in my job, I need to compare two sheets on a same Workbook, I need to know if in the second sheet a first element exists in the first sheet, and same thing for a second element always from the second to the first sheet. My problem is that it is working for 50 lines (because I use two Vlookup embeded and two conditions), BUT the two sheets have ten thousands input (lines) eache one, and the Process Excel doesn't respond for 10000 lines, I need to kill the process excel.exe because it is down.
I’ve been using excel for over 20 years now, more than half in professional life. I’ve been trying to automate stuff and decided on pursuing python for that reason. Thank you for sharing your experience and wisdom with us.
Just started to learn python on my free time. As an accountant your channel is very helpful since you give examples of how I can use python to analyze data. Keep up the good work
Former accountant turned web developer here! Derrick's awesome.
@@studywithrobin2715 existing accountant who learn Python for data analytics here. Agree to the awesomeness
if im working with large file, wilusing python be faster than the excel built in function or vba? thank u
This channel should have +1M subscribers. I am an economist from Mexico City and Derrick is my inspiration for keeping learning Python!
Been searching the world wide web for 4 hours on how to do this, and this is the only video that correct depicts what I needed. Thank you so fucking much!
Thankyou, finally found some decent content with someone who can speak english clearly. You are a saviour!
My friend, What a nice person you are and a great teacher 👏👏👏
Tx Derrick. To SQL users, that's a LEFT JOIN.
Multiple people accessing the same spreadsheet. Reduce human error. story of my life.
you deserve more views i believe that this is the future im from tijuana and all the time is consumed by making reports documents on excel thank you sir
Dude I don't usually comment on google videos however I feel inclined to tell you how useful your content is. I've watched some of your stuff and as someone new to Python that's transitioning from excel your help has been immeasurable. Thanks and keep it coming!
This video was so quick I didn't have time to thank you 👍
Thanks!
As a python newbie and advanced Excel user, I struggle to learn Python as I'm very stuck to excel logic. Would be great to have a tutorial that shows how to do most of Excel data wrangling but better and quicker with python! Thanks!
This channel is criminally under subscribed. Very very helpful!!
Chris Keo no, you
Yeah!
Agree hahaha I have created a lot of Python automated codes in my job thanks to Derrick
Excel Sumifs function from different Sheet. Thank you very much, Mr. Derrick.
After watching this video I felt compelled to subscribe. A very good video, easy to follow and most importantly very helpful. Thanks man!
Just started to learn Python. Thanks very good explanation
Tanks Derrick i’m fan of your work 👍
I really like your videos. They answer practical questions the basic tutorials don't cover.
I love how you can go across data sources.
Super thanks for sharing the code in description
Dude, you're just as awesome! The things you have shared are invaluable to people like me who are just beginning to learn Python. By far, your tutorial is best out there with all the actual real life applications. Thank you
This is a huge help! Excel is so processing heavy that python makes things so much easier. Really appreciate your videos and please keep up the good work.
Derrick, this is really helpful, it's my daily work job to find out such cases, till now I was using index match. But now I will do with the method u showed here. Thanks.
I found this at a time when I was almost giving up...Thank you
your videos ae so good. This and your other excel videos ae just what I needed!
Thank you very clear very concise very good I will watch every video you make
Thank you, Derrick!
Hi Derrick
Your explanation style and codes are simple and coo😎😎l gone through many videos on RUclips but none were practical and simple thanks bro keep it !!!👍👍👍
Have become your fan by just one vedio will explore all your content thanks again 🎉🎉🎉
Very good exercise, thanks for this great video. I appreciate and wish you the best
Very very well explained. Thanks a lot Brother. I count on your channel here after :)
URGENT!
How can I reach the excel files that you have used?
Derrick, your channel is a real gem! Please keep up the good work! You just gained a new sub
I know this is super useful content !! Derrick I can't thank you enough !!!
Superb bro. Keep doing more videos like this😎
i always use kind this vlookup in SAS, like left join and now i can do this thing with python. thank your guide again sensei
I look forward to more of your videos. I have screwed up some many formulas in excel.
@Derrick Sherrill, u are the man!
thanks for this, widens the solutions range... a faster formula is index+lookup, to eliminate human errors we can use macros. I know xlsx can't have macros but we can quickly write one in the personal macros and use it on any tybe of wb
Thanks dude.
This really help me.
Saludos!
Thanks. Very informative, quick and easy tutorial 👌. I’ve been spending lots of times trying to figure out how to do it in python until I came across your channel.
Hi Derrick, the common assumption which everyone makes is that, they assume we always work on a new excel file and the merged data we put it in last columns.. But i have an old excel file and from which i need information from a specific column. Using the vlookup its easy but python merge (the one you described) i get the entire data from the old file.. here are the steps
1. two files : old.xlsx which has 10 columns and column 3 is having sales data & the new.xlsx where i need bring in the last month sales in a newly inserted column.
2.need only only column data from old.xlsx to the new file. lets say, the location key is common and last month sales
not sure if the above describes the requirement well.. not sure how inner join, outerjoin helps but dont understand them
Pretty good video, as a begginer I could see some useful functions well explained.
Hope you do more of the videos on python. Your videos have been really helpful to me (a beginner).
Learning Python right now. Keep up the great work. I like your channel.
You save me! tks....greetings from Brazil
great content very concise
Hello Derrick .. I have learned so much from watching your videos.
Do you do any one on one training or a little Q&A ?
@Derrik Not sure why you made the distinction between a key column and a target column. Is that important? Wouldn't df_info[['ID', 'IDs']] work the same in pd.merge? The essential thing here is what we're joining on wihch the column 'IDs'
Thanks Derrick 👍. Today I'm going to try this excercise in one of my regular task.
excellent video..really helpful
Hi Derrik,
This was a nice video.
Could you please make a video on how to compare two excel files and highlight the differences in a third excel file?
Could you please help. :)
Thanks for sharing. Congratulations on your talent
you area amazing bro!!!!
Very helpful. Thanks for this.
Thank you ..can I color code the differences in columns of excel
you make me decide to learn Phyton !
Very Helpful !!
Thanks alot Derrick, you are the best.❤❤
Hi Derrick, I just wanna ask how python could locate the excel file you describe in the IDE, since there is no directory. Thanks
do you have a tutorial for index and match as well?
how to find the excel sheet you are using in the video...can i download it from them for easy learning ?
Hi Derrick,
How can I merge 2 dataframes with duplication in keys in both the dataframes and I need a FIFO allocation of 2nd df values to 1st df value.
Currently I use nested for loops, flags and it is very slow.
Great video. Just to confirm my thinking. Are you saying that by merging two tables we are achieving what vlookup would do. which in this case there was two codes that had a corresponding value in the second table. And so merging the two tables means that only values are going to be filled in that column because the code appears in both tables?
Derrick Sherrill I love you 😍
This is great. I want to compare 2 excel workbook and print all the differences.. Can you please make video on this?
Amazing!
Thank you so much, bro. This will be very useful for my project that I plan to implement in my job!
How can a xlsb workbook can be read. Don't we need any libraries to be installed? Do I understand correctly that key column in the info file was not at left side which is requirement of excel vloomup
Hi is is possible to update a vlookup formula in an existing excel using openpyxl excel alone and that can be convert as values ??
How about when I need to vlookup from two tabs? In excel I use something like IFERROR(xlookup in TAB 1, xlookup in TAB 2). How to do that? Should I place IF statement to look in other table if the output is NA?
I created a program to copy an Excel spreadsheet line into a older Microsoft Excel visual basic program . I wonder if the python program can be used to clean up the database. Is there a better way to record data.
Where can I find the A.xlsx and B.xlsx that you use above? Thanks a lot! Excellent job!
Hello Derrick, I had a question regarding using openpyxl and working with excel sheets using python. I want to link one cell from a sheet to another sheet within the excel file, so that when I click that cell, it takes me to a particular sheet in the file. I have been struggling to find a concrete solution but in vain. Is there any way in python to do the above task? Please let me know if you have any thoughts on this. It would help me greatly. Thank you.
Can you please share one by one all the excel functions and create one series as playlist. That will be great
Hi Derrick, I want to use vlookup function in existing excel sheet. Don't want to create any new data frame every time. Please help me on this with simple example. Awaiting your revert.
I just started learning python, and would like to achieve using python for excel automation.
May I ask do you import pandas into Pycharm or ?
Hi bro. Could you please make a video where you explain how to select values from different rows and colums to create a new column. For instance, in the first row select the column 3 in the second row column 5 and so on. I would really appreciate it!
This seems like a lot more effort than just typing a look up lol
This is exponentially faster especially with tens of thousands of rows to million of rows
@@Jandoesrun with that many data you would probably want to stop using excel altogether and switch to a data software
en.wikipedia.org/wiki/Automation
lol
Vlookup can’t do duplicated search?
good VOD
You ARE awesome
I need to know whether this can be automated or not: I get one raw data details on excel sheet on mail with attachment, this attached excel sheet gets automatically downloaded to certain location and then macro runs on that excel file as per schedule and then that excel sheet is send to several email addresses as per schedule without human intervention? What is required to do this automation?
how can i do the vlookup function if i have more than 1 sheet in input workbook
could it do set the inplace to false and combine another col
How do you lookup for one data that has a lot of data? For example, one ID with a lot of codes
Hi I want to print particular column which is not in row 0 but in row 6 so how do I print the values of the column
I got a case here.
I have 4 excel reports with same kind of data but with different header (column) names in each report and also one of the report has 29 tabs with similar data but for different accounts. I need to append all the 29 tabs data + data from 3 other reports into one single excel report and I would only need few common columns from all the 4 excel reports. The tab name remains same throughout and all 4 reports gets updated once a week with their respective file names suffixed with latest date.
How do we go about this using python? Appreciate your help. I have used power-query too but curious about using python.
I am coding in python and pandas since 3 days. This one solved my problem. But simply cant believe that this is the best practise solution? Pandas must have a certain command for this simply task or am I wrong? Anyway, thx a lot.
can help to compare both vlookup and phyton timing?
Hi there. Thanks for the videos. How can I create a class markbook with python?
Please make a video of appending one excel data to another excel data with similar columns. And then writing it to a new workbook
Hi Derrick, thanks for the video, it is really practical & easy. I got another query on excels which i have a few thounds of mobile number to validate for diff countries. May I know what is the best solution to calculate the invalid mobile (not in country code + mobile format)? Thanks!
Can you help me find a function in python that's equivalent to 'consolidate' function in excel ?
Hey, nice work! Thanks a lot for your effort! I‘m completely new to python. I wonder why we don‘t have to insert a full path to the excel files here or do I have to and you just skipped the info to keep the example easier?
Hi, Wen I try to print(df_3) an error pops up stating KeyError: [columnname] not in index
Pls help!
excelente video muy util
Plz guide whether vlookup method u shared on your video for python has same limitations like in excel i.e. it returns the the first record it finds or it can go to second record if first is already found earlier in the result column.
how can i transfer the data after using Vlookup into a new excedl sheet ?
Brilliant
Hi Derrick, thank you for sharing your hard skills !
I have a problem in my job, I need to compare two sheets on a same Workbook, I need to know if in the second sheet a first element exists in the first sheet, and same thing for a second element always from the second to the first sheet.
My problem is that it is working for 50 lines (because I use two Vlookup embeded and two conditions), BUT the two sheets have ten thousands input (lines) eache one, and the Process Excel doesn't respond for 10000 lines, I need to kill the process excel.exe because it is down.
Thanks Derrick :)