Before reading I'm not looking for ANY way to do this. I can do most of this already. I'm looking for a clean/efficient way to do it. By efficient I mean time wise.
Often times I have to parse excel files where the data I want to retrieve may not be in some absolute cell position.
About the files:
The cols/rows may no always be in the same order
Some spreadsheets have the headers as columns, some as rows
The labels for the rows/cols may have a typo in them
There is no guaranteed start cell. The first header may be in B2 for example
Worksheets are not necessarily in the same order, or named the same
Currently I parse the files by searching rows or columns for a particular label/header and then parse across the row or down the column to find the necessary data. This can have many issues. Using xlToRight for example will get caught on blank cells. I recognize that I could expand on this, but any solution to do so that I've come up with seems exhaustive (and looks messy!)
As far as the labels potentially being different, I've considered pattern matching but haven't thought of a good way to do it. An example of the columns from one spreadsheet to another may be something like:
Name Transaction Date Transaction Name
Name of Company Date of Trans Trans Name
That is just an example (the terrible names as intentional to throw off pattern matching).
If anyone has good solutions to the above issues I'd love to hear them.