Different file schemas, same criterias (in different collumn)

Luqman

Hi

I have few hundred excels that I need to extract data from

The columns location is not consistent, however one consistent thing is there will always be one cell with "Contribution due" there. I wish to take that collumn + 3 columns left of it.

The key data might needs "if contains" as there are whitespaces in the cell.


Sample data with Results expected attached

Location of "key data"
Excel 1 : Cell A32
Excel 2 : Cell D52
Excel 3 : Cell E49

Sample Data.xlsx

griffinwelsh

Thanks for posting this was a lot of fun to solve. Attached solution works for your sample data and should work for any set with the same format. The keys to this problem is to use a batch macro to handle each sheet one at a time and use a mutli-field formula to find the exact row and column of your key.

Transform Data.yxzp