Automatic GPO Sheet Merger

Summary

This documentation refers to the “AutoExcel” program located in M:\GPO Info\Sales Rosters GPO\COMBINE ROSTERS.

This program can be used to automatically combine GPO roster sheets into a singular Excel file. The directory of the AutoExcel application must contain the following:

• The “GPO Folder” folder
• The “source_settings” text file

Without these, the program will not work.

GPO Folder

The GPO Folder contains many sub-folders, each one corresponding to a specific source organization (Ex: Avera). The names of the folders are important, as they’re referenced within the settings file.

You can include as few or as many files in the sub-folders as you want, but every file that follows the proper formats will be read. This means that if you have any files with duplicate information, it will still be read and inserted into the resultant file.

The formats include:

• .xls files
• .xlsx files
• .csv files

Source File Rules

There are a few rules that need to be followed for the files to be read correctly:

1. Only the first sheet will be read from a file. If you want to have multiple sheets read, create multiple files with one of the subsequent sheets accordingly.
2. There must only be one rowabove the entries. Data to be read must begin on row 2.
   1. The top row MUST include the header names, as it’s the way the program merges.
3. The column names must be consistent within a folder.
4. Street addresses should be merged. (Street Address + “Suite”, “Apt”, etc. should be one column)

Settings File

The settings text file allows you to create rulesets for every sub-folder in the GPO Folder.

The above example is the “Template” example. The first line is the name of the sub-folder. The left side of the data refers to the specific column names as they appear in the Excel file being read. The right side is what the program converts the columns to so that they can match with the others in the final sheet.

The “Source” can be changed to any identifier you want in the final sheet to tell you where an entry came from.

DO NOT CHANGE THE RIGHT SIDE VALUES.

The following is another example to draw comparisons and see how editing the file looks:

The name of the folder in “GPO Folder” is “BuyersEdge”. The file used in the merge uses the column “Location Name” to refer to an entry’s name, “Address” to refer to the street address, etc. The “Source” value is “Buyers Edge”, which does not come from the sheet.

You can add as many entries as you want for new sources. Every single file within the sub-folder must follow the settings entry’s rules, though, or there will be empty rows in an export.

Using the Program

By opening the program (AutoExcel.exe), you’ll start the process of merging the Excel files. Depending on how much data there is, it can take some time. The data is pulled, combined, and color-coded to mark different sources.

Once the program is done, it will close on its own and a new file will be created in the directory with the current date. If you attempt to generate a file while one already exists with the same date, it will be overwritten. If you intend to create multiple compound GPO sheets in a day, be sure to rename your files as you make them to avoid losing work.

The file might appear in the folder while the program is running. Do not interact with it until the program is complete.

Example

This example illustrates the usage of the program. Here is a screenshot taken from the Avera GPO sheet:

That source file has the following columns:

• Account Name
• PACE Affiliate Type
• Clinic Type
• Other PACE Affiliate Type
• Street
• City
• State
• Zip

Our sheet tries to use only what’s generally common information between the GPO rosters. Because of this, we’ll be looking to use specifically:

• Account Name
• Street
• City
• State
• Zip

These two images are the Avera settings next to the Template settings. You can see that Avera is missing an entry: Internal ID.

The Internal ID column is used for any identifier numbers that a source might use to reference their entries. Avera doesn’t use them, so it’s simply excluded from the settings. You can exclude anything that isn’t included in a source’s files but try not to add anything that isn’t in the Template unless you are prepared to either find matches in other files or have a column that only one source fills.

Make sure to save the settings file.

With the settings added, the next thing to do is create the sub-folder within the GPO Folder.

The “Avera” folder is created within GPO Folder and named to match the first entry in its settings. Any sub-folder with a name that isn’t able to be found in the settings will be skipped.

Within that folder, place your Excel sheet:

You can then run the “AutoExcel” program to see the following:

When the program is done, it will close and the directory will now have a “GPO Roster (current date)” Excel file.

This is the combined file:

The file took the columns from the source sheet and inserted them into the combined file where they matched in the settings. If you scroll towards the bottom:

The color changes to blue and the source (second to last column) now reads “Template”. This means that the entries from Avera have ended and now the file is showing the entries from the Template file. Since Avera didn’t have any “Local ID” equivalent, it’s left blank. The template, however, does have IDs ranging from 1-6.

The program also makes sure to create a copy of the source and place it as its own separate sheet at the bottom in case you need to access an individual source.

The program will automatically size column widths to match the values being fed to it. This makes it very readable for the user where the source might not be (Like in the Avera file).