There is no single "right" way to clean up messy client exports, but there is a smarter way to think about the problem. The user's frustration with column order and inconsistent headers is not a technical failure; it is the natural result of working with tools that were never designed to talk to each other. The solution is not to memorize another formula or build a fragile VBA macro. It is to step back and standardize the *process* of standardization itself.
Here is what we mean. Power Query is the right starting point, not because it is powerful in the abstract, but because it separates the act of cleaning from the act of importing. Instead of writing INDEX/MATCH lookups that break the moment a header changes from "Name" to "Full Name," you can build a single query that maps common variations to a canonical set of columns. That is the practical shift: you are no longer reacting to each file as a unique event. You are building a translation layer. The first time you invest an hour in Power Query, you are buying back that hour every time a client sends a new variant. That is the trade-off worth making.
The user's instinct to avoid VBA is correct, but not for the reason they might think. VBA is reusable, yes, but it is also opaque. It hides the logic in a way that makes it hard for a colleague to audit or for the user themselves to revisit six months later. Power Query, on the other hand, shows its steps. It is self-documenting. When a column name changes slightly, the query either handles it through fuzzy matching or surfaces a clear error that points to the exact step. That is not a minor convenience. It is the difference between a workflow that adapts and one that breaks silently.
The real takeaway here is that messy data is not a personal failing. It is a constant of working with external parties. The question is not whether you will see inconsistent exports again. You will. The question is whether you have a repeatable method for absorbing that inconsistency without losing your afternoon. Start with a single query that handles the two or three most common column orders you see. Map them to your standard. Then add a fourth column order when it appears. You are not building a perfect system. You are building a habit. That habit is what turns a recurring annoyance into a background task.