At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
Hi, and welcome back. In the previous episode we tidied hundreds of files. Now we go inside the files: processing many Excel files at once. We'll ask Claude Code to check the format differences, merge a dozen files into one master table, and make sure no numbers go missing.
Step 1: A dozen files, different formats
At the start of every month, Dewi receives fourteen sales files from branch heads. Look at three examples. Even the sheet names are all different. The quantity column is called Qty in Bandung, Quantity in Bekasi, and Qty pcs in Bogor. Same meaning, different names. A person understands this easily, but a normal Excel formula doesn't know that all three are the same column. Bekasi adds two title rows above the table, and Bogor adds one. If you merge them as they are, the titles get read as data. Bekasi also writes prices as text, complete with Rp and separators. Excel can't add those up. And two branches put a total row under the table. If it gets merged too, sales are counted twice. Until now, Dewi has copied each file into the master file by hand. It takes about three hours every month. The bigger danger isn't the time, it's the mistakes. A single total row copied by accident is enough to send a wrong report to the director.
Step 2: Ask Claude to check first
Dewi copies all fourteen files into a working folder, then asks Claude to check them all without changing anything and build a format difference report. Claude writes a small checking script and runs it. Thirteen files are readable, and one is password-locked. The summary is clear: four different names for the quantity column, six files with extra title rows, five files with a total row, and one file with prices stored as text. For the locked file, Claude doesn't guess and doesn't ask for the password. That's right: passwords should never be typed into a request or stored in a script. Dewi simply opens that file personally and saves a copy without the lock. All findings are saved as an Excel file, one row per file, so they're easy to check. Dewi opens the report in Excel. Bandung is the tidiest: a Qty column, no title rows, no total row. Its note is a single word: ready. Bekasi has two title rows, a total row, and prices stored as text. This is the file that most often causes wrong totals. Bogor uses the column name Qty pcs with one title row. Depok is still a question mark because it's locked. Tangerang uses a fourth name, Count, and has two empty rows. With this report, Dewi knows exactly what needs to be standardized.
Step 3: Agreeing on rules and merging
Dewi has saved the Depok file again without the lock. Now all fourteen files are ready. Dewi writes the standardization rules in everyday language: four column names become one; title, total, and empty rows are dropped; text prices become numbers; and a Branch column is added from the file name. Claude repeats its important promise: the original files are only read, and the result is saved as a new file. For that it needs two Python libraries, which are add-on tool packages for reading and writing Excel. A permission box appears to install the libraries. The pip install command is the standard way to install Python packages. Since it's clear what it does, Dewi chooses Yes. Claude writes a merge script of about seventy lines. Then it runs it. The result is two thousand three hundred eighteen data rows, with total rows and empty rows dropped automatically. Here's the master table. Every row now has a Branch column, so the source of the data is always clear. If a number looks odd, Dewi knows which branch head to contact. The Quantity column is uniform, whatever its original name was in the branch file. The Bekasi data, which used text prices, now holds plain numbers. Because they're numbers, the Total column can be calculated and added up. The Bogor data follows, without title rows and without the total row. The same six columns for all fourteen branches, ready for reporting.
Step 4: Reconcile the totals, don't just trust it
The table looks tidy, but don't just trust it. Dewi asks Claude to compare the total of each source file with the total per branch in the master table. The result: thirteen branches match, and one is off by four hundred fifty thousand rupiah. Good thing Dewi checked. Without this step, a small difference like that easily slips into the director's report. Claude explains the cause. Three rows in the Tangerang file have an empty quantity column, even though their total is filled in. The script thought they were empty rows. Dewi decides the rule: a row only counts as empty if all of its columns are empty. Claude fixes two lines in the script. Then it merges and compares again. All fourteen branches match, with a difference of zero rupiah. Now the master table is fit to use. Let's look at the final result. All fourteen branch files were read. Two thousand three hundred twenty-one data rows. That's three more than before: the Tangerang rows that were dropped earlier. A total difference of zero rupiah. This is the number that gives Dewi the confidence to send the report. And it took about ten minutes, compared with three hours of copying and pasting every month.
Step 5: Ready to use every month
To keep this script useful for a long time, Dewi asks for four rules. First, every month uses a new folder, and the original files are never overwritten. Second, the total check always runs too, not just today. Third, if a branch uses a new column name, the script stops and reports it. Stopping is better than guessing. Fourth, file passwords are never written in the script. Locked files are opened by hand by an authorized person. Let's see those rules at work. A month later, Dewi opens the October sales folder. Dewi just asks Claude to use the same script, then compare the totals. No need to explain all the rules again. The script stops. There's a new branch, Cirebon, which uses the column name Units. The script doesn't guess, exactly as requested. Claude asks whether Units means the same as Quantity. Decisions about what data means stay in human hands. Dewi says yes and asks for that name to be added to the list. The script runs again. Now there are fifteen branches, all matching, with a difference of zero rupiah. The script grows along with the business, little by little. This pattern works in many places. Mr. Hendra at Cerdas Tutoring could merge grades from every class into one summary per student. HR could merge each division's attendance file into a monthly summary. The warehouse team could combine stock reports from several locations. The steps are the same: check first, agree on rules, merge, then reconcile the totals. The screens in this video are simulations, so details in your Claude Code may differ slightly.
Done!
We merged fourteen Excel files with different formats into one master table, found a hidden difference, and made sure the totals match. In the next episode, we turn this master table into a monthly report, complete with a summary and charts, automatically. See you there!
Practice exercises
Try these on your own after watching to lock in what you learned.
Ask Claude Code for a format difference report on three similar Excel files of your own
Write rules for standardizing column names and ask Claude to merge those files
Ask for a total check per file and compare it with the numbers in the original files
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,286 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
