How I Automated Repetitive Work with GPT (Excel Cleanup Edition)
Every Monday, I was merging Excel files received from three departments, removing duplicates, matching the format, and creating a file for reporting. It took about two to two and a half hours, and I kept thinking, is this really something a person should be doing?
At first, I just started with "Write me Python code to merge Excel files," and it failed. Of course it did, since I hadn't told it the actual column names or the edge cases. So I changed my approach to this:
- Paste in the actual column names and three sample rows as-is
- List the edge cases first (empty rows, inconsistent date formats, different department name spellings)
- Ask for the output to be broken into functions
- If an error occurs, paste the entire error message back in as-is
After about four rounds of revisions, I got working code, and now it finishes in three minutes with a single button click. Honestly, I don't understand the code. But since I know best what needs to go into it, the key was explaining only that part accurately.
The conclusion is that it's not about writing good prompts, but about whether you can organize your work as if explaining it to someone else. If you can't do that, nothing comes out no matter what you ask AI to do.
10 answers
Agreed, that's the key point. It's not prompt engineering—it's my ability to explain my work.
I did something similar, and the results changed the moment I pasted the actual column names exactly as they were lol. If you speak in abstractions, even GPT can't do anything about it.
But honestly, isn't it kind of risky to use code you don't understand? It might work fine now, but if a column gets added or the format changes later, no one will be able to fix it. And if the person who built it leaves later, the department will be in an uproar.
How did you get it to handle things like empty rows or inconsistent date formats? I had to redo mine a few times because of that too.
When you paste three sample rows, don’t upload real employee names or account numbers as-is. That’ll trigger a privacy incident at the company. I recommend keeping only the column names and swapping the values for dummy data.
You've really nailed the key point. Ultimately, tools are just tools, and it seems the person who precisely defines the work is the one who wins. I also automated month-end closing in a similar way.
If it saves you 2.5 hours once a week, that's 10 hours a month, but when you factor in the time spent building and debugging it, you're actually at a net loss at first lol. Of course, if you use it for a year, it pays off.
Got a source? What tool did you run it with?
I totally relate to inconsistent department name formats. Once I set up a mapping table for "Sales Team 1," "Sales 1 Team," and "Sales1," the stress disappeared after that. And copy-pasting the entire error message makes a surprisingly big difference too. If you trim off the beginning and end, GPT ends up fixing the wrong part.
Well, I'm not so sure about that. These days, that kind of thing takes just five minutes with Power Query, so I question whether it makes sense to go out of your way to get code from GPT and end up with unmaintainable code. The tool choice is a bit disappointing.