AML Report

A Python application that processes and analyzes Excel files containing component specification data, highlighting differences across multiple variants of the same material.

Overview

AML Report (Approved Manufacturer List Report) is a utility tool designed to streamline the analysis and comparison of component specifications across different materials and manufacturers. The application reads data from Excel files, identifies variants of the same component, and visually highlights differences in key specifications using color coding.

Purpose

This tool is particularly useful for:

• Component Comparison: Identify and visualize differences in specifications (Length, Width, Thickness, Tape width, Pitch, Polarity) across multiple variants of the same internal material number
• Data Organization: Extract relevant component information from complex Excel sheets and organize it in a clean, structured format
• Quality Control: Easily spot inconsistencies or variations in component specifications
• Supply Chain Management: Manage approved component variants and their specifications for manufacturing purposes
• Visual Analysis: Color-coded highlighting makes it easy to identify differences at a glance

Features

• Flexible Column Matching: The application uses intelligent column detection with:

  • Exact normalized matching
  • Inclusion-based matching
  • Fuzzy matching for approximate column names

• Automatic Grouping: Data is automatically grouped by internal material number for easy comparison

• Visual Highlighting: Cells with differing values are automatically color-coded with distinct colors for each unique value:

  • Soft Yellow (FFF176)
  • Coral (FF8A65)
  • Mint (A5D6A7)
  • Light Blue (90CAF9)
  • Lavender (B39DDB)
  • Amber (FFD54F)

• Professional Formatting:

  • Excel table styling for enhanced readability
  • Auto-adjusted column widths based on content
  • Built-in filtering and sorting capabilities
  • Organized layout with blank rows separating material groups

• User-Friendly Interface: Simple file selection dialogs for input and output

How It Works

1. Select Input File: The application opens a file dialog to select your Excel file

2. Parse Data: Reads the "CE rev" sheet and intelligently matches column headers to extract:

   • Int. material no.
   • Manufact.
   • MPN
   • Length
   • Width
   • Thickness
   • Tape width
   • Pitch
   • Polarity

3. Group and Analyze: Groups data by internal material number and identifies specification variations

4. Apply Formatting:

   • Highlights cells with different values using color coding
   • Formats data as an Excel table
   • Adjusts column widths for readability

5. Save Output: Saves the processed data to a new Excel file with the "CE" sheet name

Requirements

• Python 3.x
• openpyxl
• tkinter (usually included with Python)

Installation

Install the required package:

pip install openpyxl

Usage

Run the script:

python AML_Report.py

The application will:

1. Open a file dialog for you to select an Excel file
2. Process the data from the "CE rev" sheet
3. Open a save dialog for you to specify where to save the output file
4. Create a new Excel file with organized, color-coded data

Input Format

The input Excel file must contain a sheet named "CE rev" with the following columns (exact names not required due to flexible matching):

Column	Description
Int. material no.	Internal material identifier
Manufact.	Manufacturer name
MPN	Manufacturer Part Number
Length	Component length specification
Width	Component width specification
Thickness	Component thickness specification
Tape width	Tape width specification
Pitch	Tape pitch specification
Polarity	Polarity specification

Output Format

The output Excel file will contain a "CE" sheet with:

• All specified columns from the input data
• Data grouped by material number with blank row separators
• Specification columns (Length, Width, Thickness, Tape width, Pitch, Polarity) color-coded where variations exist
• Excel table formatting with filtering capabilities
• Auto-fitted column widths

Example

Input (CE rev sheet):

Int. material no.	Manufact.	MPN	Length	Width	Pitch
MAT001	Supplier A	ABC123	10mm	5mm	1.27
MAT001	Supplier B	XYZ789	10mm	6mm	1.27

Output (CE sheet): The same data is reorganized with:

• Color coding on the Width column showing different values (5mm and 6mm)
• Blank row separating groups
• Table formatting and filters enabled

Created By

• Jesus Enrique Zarate Ortiz
• Luis Fernando Monjaraz Briseño

License

This tool is provided for internal use in component quality and supply chain management.

Notes

• The application is designed to work with the specific "CE rev" sheet name
• Column names will be automatically matched, allowing for minor variations and extra spaces
• The output file is created as a new file, leaving the original unchanged
• Color coding is applied only to rows where variations exist within a material group