VBA Code for Index Match (multiple criteria) on Customer Data Sheet - Data Enrichment

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Let me guess at how you are getting this report. You click a button on the BI tool and it writes the Excel file for you. I work with a similar system. However our IT department has made a shadow database against which we can write SQL statements. I use MS-Query to import this data from the database directly into Excel. Most organizations are not set up to do this.

So I am going to suggest a second best strategy. Can you have your IT department download the report automatically each day (or whatever) to a specific directory in CSV format? I can coach you through setting up MS-Query to read this CSV file. I can even provide some code so that if the file has a different name each day (due to a date stamp), that will change the connection string so you always access the latest downloaded file.

One of the benefits of MS-Query is that it imports data into Excel tables. The import mechanism clears out the old data and imports the new data into the table. Tables "remember" formulas, formats and validations associated with them and copy them down automatically. So all the columns from AD on can have validations or VLOOKUP formulas. I don't see any out there right now.

I can also coach you through setting up the report to run automatically using the task scheduler.

So here is a potential scenario: Your IT department downloads the CSV file to the directory at 2 AM. Your report kicks off at 5 AM. You come to work in the morning, open up the report and start filling it in.

Do you think such a setup would be possible in your organization and will you be willing to learn MS-Query and the Windows Task Scheduler?

Second best is that they mail you the report in CSV format and you save it to a specific directory. Third best is you download the report as a CSV file manually. Fourth best is to work with what you have now, but I'll need more definition about what is supposed to go in columns AD onward. So far it all looks like manual entry to me and I can only guess at what data validation you need.

The BI tool should have the capability to do both a download and mailing. Whether your IT people will do it depends on corporate policy.

Importing data will still take some time, however, it will eliminate a lot of VB code. You may need some scraps of VB code to tie things together.

Let me know how you would like to proceed.