Excel Project: RFMAC & RFHSC Data Review and Standardization Project Objective The purpose of this project is to compare how RFMAC and RFHSC enter and organize their data,

Excel Project: RFMAC & RFHSC Data Review and Standardization

Project Objective

The purpose of this project is to compare how RFMAC and RFHSC enter and organize their data, identify similarities and differences between the two datasets, and create one standardized Excel template that can be used for future reporting and analysis.

You will review both files, map corresponding fields, standardize the column headers, identify any differences in fund codes, and combine the data into one consistent Excel table that can be used to create PivotTables. You can also create a PivotTable itself.

Part 1: Review and Compare the RFMAC and RFHSC Data

RFHSC

1. Review Columns B through R in the RFHSC file.
2. Review the data by Sponsor Name to determine how information is entered for each sponsor.
3. Identify:
   • Fields that are the same or similar to RFMAC.
   • Fields that are different.
   • Fields that RFHSC has that RFMAC does not have.
   • Fields that RFMAC has that RFHSC does not have.
4. Pay particular attention to any fields highlighted in red in the template attached and document what makes these fields different or significant.

RFMAC

1. Review the RFMAC data and compare its fields and data-entry practices with RFHSC.
2. Do not remove any RFMAC fields that are not available in RFHSC. These fields must remain in the final template.
3. Compare the fund codes used by RFMAC and RFHSC.

Fund Code Mapping

The following fund codes are considered equivalent:

Important: Make note of any fund code that does not match or does not have an identified equivalent between the two systems.

Part 2: Standardize the Column Headers

Use the provided template as the starting point for the standardized dataset.

1. Include all fields

Bring every column header from both the RFMAC and RFHSC files into the template.

Do not delete a field simply because it only exists in one of the two files.

The final template must contain all required fields from both datasets.

2. Use RFMAC as the standard column order

The RFMAC column order must remain unchanged.

Do not rearrange the RFMAC columns.

Instead, rearrange the RFHSC columns so that they follow the same order as the RFMAC columns.

For example:

RFMAC:

If RFHSC is organized as:

Rearrange the RFHSC columns to:

3. Standardize similar headers

If an RFHSC header represents the same type of information as an RFMAC header, change the RFHSC header so that it matches the RFMAC header exactly.

Example:

• RFHSC: Return of Fund Requirement
• RFMAC: Return of Fund

If these fields contain the same type of information, change the RFHSC header to:

Return of Fund

The goal is for equivalent fields to have identical column headers in the final template.

4. Handle fields without a duplicate

If a field exists in only one dataset and does not have a corresponding field in the other dataset, keep the field.

Move any unique/non-duplicate fields to the end of the columns in the final template.

For example:

Do not delete unique fields.

Part 3: Verify the Column Headers

Use the Excel =EXACT formula to verify that corresponding column headers match exactly.

For example:

=EXACT(A2,B2)

The formula will return:

• TRUE = the two headers are exactly the same.
• FALSE = the headers are different.

Use this to check that the standardized headers have been correctly matched.

Before moving on, ensure that all corresponding RFMAC and RFHSC fields use the same header name and wording.

Part 4: Transfer the Data into the Template

Once you have completed the column mapping and standardized the headers:

1. Use the finalized template as the master spreadsheet.
2. Copy all data from the RFMAC file into the appropriate columns.
3. Copy all data from the RFHSC file into the corresponding columns.
4. Ensure that the RFHSC data follows the RFMAC column order.
5. If a field does not exist in one dataset, leave that field blank for those records.
6. Apply the identified fund-code mappings where appropriate:
   • RFMAC Fund 50 ↔ RFHSC Fund 80
   • RFMAC Fund 55 ↔ RFHSC Fund 85
7. Ensure that no records or fields are accidentally omitted during the transfer.

Part 5: Create the Final Excel Table

Once all RFMAC and RFHSC data has been combined:

1. Select the complete dataset.
2. Convert the dataset into an Excel Table.
3. Ensure that:
   • Every column has a clear header.
   • There are no blank column headers.
   • The column structure is consistent.
   • Data is entered in the correct columns.
   • There are no unnecessary blank rows or columns within the dataset.
4. The table should be structured so that Sonya can create a PivotTable directly from it for quick reporting and analysis.

The final table should allow for reporting such as:

• Data by Sponsor Name
• Data by Fund
• Comparison of RFMAC and RFHSC records
• Totals by fund
• Identification of unmatched or unique fund codes
• Other relevant summaries that can be generated through a PivotTable

Final Deliverable

Your completed Excel workbook should include:

1. Data/Column Analysis

A record of the similarities and differences identified between RFMAC and RFHSC, including any important differences in data entry.

2. Standardized Template

A new template containing all required columns from both files, using RFMAC's column order as the standard.

3. Combined Dataset

All data from the RFMAC and RFHSC files entered into the standardized template.

4. Fund Code Mapping

Documentation of:

• RFMAC Fund 50 = RFHSC Fund 80
• RFMAC Fund 55 = RFHSC Fund 85
• Any additional fund codes that do not have a matching equivalent.

5. Excel Table

A clean, standardized Excel Table that can be used directly by me to create PivotTables and perform reporting.

Overall Goal

By the end of this project, someone should be able to look at the final spreadsheet and not need to know whether a record originally came from RFMAC or RFHSC to understand the data structure. Equivalent information should use consistent headers, the columns should follow a consistent order, and the entire dataset should be ready for reporting.

Please explain in detail all the work you did as well as what your final analysis is between how RFMAC and RFHSC enters their data.