Automate Excel using Python | Excel Hacks with Pandas

This video is about Excel Hacks with Pandas, automate Excel using python and how to split an excel file into multiple sheets. Also learn to excel hacks 2019

👉 Instagram: @theaiwarriors

Favourite Mobile Apps to Run Python and Code

Song

Credit To Author

Support Cavardix Beats:

-----------------------------------------------------------------
Royalty Free Music - Shadows (Dark Underground Rap Beat Hip Hop Instrumental)

[jcventura]

For those of you asking: YES it can be done with the native Excel tools. Nevertheless, tutorials like this one are great for adding new tools to our toolbox, for certain things some programming languages are simply better suited than others, it's good to have more than 1 weapon in your arsenal.
Two ways to solve this natively:
a) If the categories are not too many, a quick pivot table with anything as values and all the categories as rows will do the trick. Simply double-click in the subtotal for each row and a new sheet will be created with all the table rows pertaining to the specific division only. Rename the sheet and it's done
b) VBA, you can use something like the following macro to get it done, it prompts for the user to provide the table range (including headers) as well as for the user to provide the column to be used as categories (as a string input). You can modify the code if you feel like, it's a bit sloppy but it will do the job.

Sub splitBy()
Dim table, header, colHeader, activeItem, activeRow As Range
Dim field As String
Dim nTimes, currentRow As Long
Dim sht As Worksheet
Set table = the table including headers", Type:=8)
field = column name", Type:=2)
Application.ScreenUpdating = False
nCols = table.Columns.Count
nRows = table.Rows.Count - 1
Set header = table.Resize(1, nCols)
Set colHeader = header.Find(field)
table.Sort key1:=colHeader, order1:=xlAscending, header:=xlYes
currentRow = 1
Do While currentRow <= nRows
Set activeRow =
Set activeItem = colHeader.Offset(currentRow, 0)
If currentRow = nRows Then
Set sht =
With sht
.Name = activeItem.Value
.Range("A1").Resize(1, nCols) = header.Value
.Range("A2").Resize(1, nCols) = activeRow.Resize(1, nCols).Value
End With
Exit Do
End If
nTimes = Application.WorksheetFunction.CountIf(colHeader.Offset(currentRow, 0).Resize(nRows - currentRow, 1), colHeader.Offset(currentRow, 0))
Set sht =
With sht
.Name = activeItem.Value
.Range("A1").Resize(1, nCols) = header.Value
.Range("A2").Resize(nTimes, nCols) = activeRow.Resize(nTimes, nCols).Value
End With
currentRow = currentRow + nTimes
Loop
Application.ScreenUpdating = True
End Sub

[patricialim]

Coming from a business background, found myself in a situation at work where I can use excel/python to transform our processes - wish me luck!

[KleanthisSkoulikaris]

Dude, you’re a GOD! I just stumbled across your videos recently and I love your Excel/Pandas/SQL videos. If I study your code carefully, I could potentially adjust it and use it in my work as well, where I often have to run similar scenarios.
You got yourself a new dedicated subscriber!

[jamesvalery]

Excellent job!! I wrote a program pretty much exactly like this for a back end batch process that just dumped to a staging table in the morning. All the execs wanted to view it by market, division, and location type so I had to do a couple of groupbys in pandas on a few more columns. Nice to know I am not the only one building little tools like this for work that save so much time lol.

[bhthllj]

Awesome, thank you! Although I would suggest casting the sheet names to strings, as I ran into type errors while trying out your script. But the rest was spot-on!

[ramyakuntay]

That’s a great video. Kindly show us how you schedule the split excel emailed to concerned owner. That will be really useful and thanks for sharing the knowledge. Your videos boast up the confidence. 🙂

[hrshkdm]

This was really helpful. Thanks a lot !!

[tcbtrvpsrno]

Nice work clearly explained! If we are using pandas then it would be easier to use the groupby function in pandas!

[maxbart]

i just wanna thank you for your code and your videos

[ammarwasif]

amazing stuff.. your videos deserve more views.

[MrRafal]

Hey, is it possible to keep the formulas when spliting to new files?

[jonathanfriz]

Thanks man, very clear. Also thanks for sharing. One doubt. The file merge follow some order for dates of that instance? or is all of the first category then all the second category and so on? of course you can sort it later on excel.

[jasonng]

Gonna do this for my dropshipping report...

[radams]

Love the video. This is my first of many videos, I want to watch to improve me using Python for Excel. Quick question though, if I wanted to sort the sheets alphabetically, where would I insert that? The column segmentation I have has 20 columns and they are all out of order. I plan on watching more of your videos later today. Thank you.

[jariuslouie]

New subscriber here. Thanks for all these amazing content that you have.

[abdulmgeedshesha]

what is the point for the first (while True ) in the last cell, and thankyou

[sudharshan]

Hi Sir, this is what exactly i was looking for but finaly i found in your channel, thnak you for making this video.

i am not able to open code.
when i click on the link in discription Git hub says

"Uploads are disabled.
File uploads require push access to this repository."

please help

[karunakaransabapathy]

Its very useful but not working for me getting invalided argument while giving input file.

[mtranjan]

Not able to download code from GitHub. Can u help me with the code.
Thanks in advance., 🙏

[DavLim]

The technique used will also affect the runtime. If u try to filter and split, instead of per cell check, it will greatly improve the runtime