An automated process to:

1- Import data from Raw data file after checking errors

2- Convert it into Excel friendly database

3- Create Main template along with Clients data files based on templates (Each with his own data)

4- Import data again after the clients returned thier files

AnRuch is devided into main 4 steps:

Step 1 - Reading Raw data file and check it for errors using PivotTable features for checking all types of errors

Error types included:

- Splited tables, or missing rows

- Column Error, or missing titles, columns

- Empty cells, cells should not be empty

- Client names that is not found in original DB

- Checking for IDs that are enterred as text (since this will affect how AnRuch work with it)

- Duplication error, find if there are duplicated rows that they should not be

- Summation error, If the user played with some functions that are already set in the template

- Consistency error, Check if the user have made some mistakes enterring each client name with his company name and ID.

- Period error, check if the user enterred invalid values, such as Month-to-Date amount larger than Year-to-date amount

Doing all these errors for 6 sheets of the Raw data file.

Logging the time the check needed to finish, errors encounterd along with the row number to tell the admin what is the row that has each error.

Step 2 - Create Templates (Both type of templates, Main template and Client template) using PivotTables. Clients templates depend on the number of clients saved in the main AnRuch file.

Creating the Main template (already in two types) based on set of 4 templates each (one for each quarter of the year) alread saved and ready to use.

Using PivotTables again to sum the totals and get the unique record for each client and collect them into one big database.

Create the Main template file that holds the whole clients data after the PivotTable.

Then go through the list of clients already saved in the Main AnRuch file to create each clients quarter file, remember 6 sheets per file.

Save the output files for all of the clients into the "Output" folder after creating it with the date mask for that period.

Step 3 - Collecting data (Used after the clients returned thier files back) to collect the numbers and merge them into the Main template

Step 4 - Dashboard creation, 8 types of dashboards each with its set of variables and statistics based on the Main template created in Step 3.

This was a good project and took around 4 months to finish