VBA Macro - Automating Data Extraction and Printing of Excel Tables

Budget $30-250

Posted: 2 year ago

Opened

Description: Project background:
Our company receives water testing results from an analytical laboratory in the form of an excel spreadsheet. The results are numerical and are split into several different analytes (lead, mercury, etc.).

We present these results in our own formatted tables, both as a table for the results in each instance, as well as a table showing the trend in results. Results are split by the two different water types that we test for, which is recorded in the excel spreadsheet from the laboratory. The project goal is to automate the transfer of the results from the excel spreadsheet provided by the laboratory into our own excel spreadsheet tables and print our formatted tables for delivery to the client.

Examples of the input (laboratory spreadsheet), the current VBA macro and our formatted tables will be provided for development.

Current state of project:
We have built VBA macro which performs the transfer of results into our own per instance and trend tables by copying particular cell locations in the laboratory's excel spreadsheet, although some issues are to be resolved. These are:

The laboratory changes the order of the particular analytes tested (lead, mercury, etc.), making transfer by cell locations erroneous, therefore,

- The macro must be updated to instead search for a particular phrase (lead, mercury, etc.) in the laboratory's excel spreadsheet and copy the result to our tables. This way, if the order of results change from the laboratory, the macros functionality remains.

Where more than one sample is taken, the laboratory will send multiple 'sets' of analytes, therefore,

- The macro must be updated to, when finished searching and copying results as above in (1), check the next column and determine if further analytes are present. If there are, this next set is to be copied to our tables as well.

The copying of several sets of analytes as above in (2) as implemented should place each new set on the next row of the trend table, however, the per instance table will be overwritten, therefore,

- Between each new set of analytes, the macro must print the per instance table prior to being overwritten. Once no more sets of analytes are present, the macro must then print the updated trend table.

Deliverables
VBA Macro which:

consistently copies sets of analytes from the source (laboratory spreadsheet) to the destination (company formatted spreadsheet/tables) correctly by searching for text.

Following being copied, prints the existing company formatted spreadsheet/tables as expected, with tables for each set of analytes copied and one final updated trend table.

Category: Excel Macros

Source: freelancer.com

Add a bid

Similar freelance jobs

Image Data Collection Project

We are seeking a reliable freelancer or team to assist with an image data collection project for text recognition. The task involves collecting real-world photographs containing visible text from various everyday sources. These images will be used to build a high-quality dataset for a text recognition......

Text Recognition

23 hours ago

Image Data Collection Project

Text Recognition

1 day ago

Image Data Collection Project

Text Recognition

2 days ago

Image Data Collection Project

Text Recognition

3 days ago

Image Data Collection Project