Member List Change Tracker

This project is designed to analyze changes in member lists across six teams and identify potential inconsistencies, such as the same member being listed under multiple teams. It is particularly suited for scenarios where team member lists are frequently updated and cross-referenced for accuracy.

Disclaimer
The example data provided in this repository is completely fictional and was generated using random data generation techniques. This data is not related to any real individuals or organizations.

Features

• Track Additions and Removals: Compares two Excel files (before and after versions) to identify members added or removed from each team.
• Detect Overlaps: Highlights members appearing in multiple teams to resolve potential conflicts.
• Format Standardization: Ensures phone numbers are standardized for easier reading and comparison.

How It Works

1. Input Files: The script takes two Excel files as input:
   • file_before.xlsx: The member list before changes.
   • file_after.xlsx: The member list after changes.
2. Comparison: For each team (represented as sheets in the Excel files):
   • Members added or removed are identified.
   • Results are formatted with name and phone number.
3. Conflict Detection: Identifies members appearing in multiple teams and lists the associated teams.
4. Output: Prints the results in a structured format for easy review.

Sample Data

The project uses sample data for demonstration purposes:

• Names: Randomly generated using dictionaries of foreign first and last names.
• Phone Numbers: Randomly generated in Turkey's phone number format (e.g., +90 XXX XXX XX XX).
• To create this randomly generated data, the anonymize_data.py script from the current repo was used. You can see the process of generating the random data from the script.

Example Excel Files

Two example Excel files (file_before.xlsx and file_after.xlsx) are included to demonstrate the script's functionality. These files do not contain real data.

Script

The script responsible for this functionality is analyzer. It is written in Python and utilizes the following libraries:

• pandas: For data manipulation.
• openpyxl: For reading and writing Excel files.

Data Anonymization Script

To ensure no real personal data is used, a separate script named anonymize_data.py is provided. This script:

• Replaces names with randomly generated foreign names.
• Generates Turkish-format phone numbers randomly.
• Outputs the anonymized data into a new Excel file, preserving the structure of the original file.

How to Use the Anonymization Script

1. Place your original Excel file in the project directory.
2. Run the script:

   python anonymize_data.py

3. The output file will be saved as a new Excel file (e.g., anonymized_file.xlsx).

Example Output

Below is a sample output generated by analyzer.py:

### ZARA
#### ADDED MEMBERS
- Roy Smith: +90 518 569 97 25
- Benjamin Cook: +90 521 718 20 10

#### REMOVED MEMBERS
- Eric Green: +90 537 560 15 95


### FIONA
#### ADDED MEMBERS
- Gerald Lee: +90 525 990 51 89
- Patrick James: +90 515 384 32 31
- Brandon Moore: +90 536 943 88 95
- Bruce Martinez: +90 539 317 79 45
- George Young: +90 552 310 71 77

#### REMOVED MEMBERS
- Henry Reyes: +90 535 465 69 30


### ELLIOT
#### ADDED MEMBERS
- Paul Myers: +90 528 478 83 79
- Alexander Thompson: +90 530 862 98 28

#### REMOVED MEMBERS
- Brian Ward: +90 523 823 62 48


### ETHAN
#### ADDED MEMBERS
- Dennis Kim: +90 556 284 34 40
- Dennis Kim: +90 529 408 33 70
- Larry Allen: +90 557 470 66 41
- Frank Diaz: +90 545 636 65 29

#### REMOVED MEMBERS
- Eric Thomas: +90 528 722 48 63


### LANA
#### ADDED MEMBERS
- Brian Ward: +90 523 823 62 48
- Joe Watson: +90 559 622 34 78
- Jeremy Nelson: +90 521 562 77 08
- Joseph Alvarez: +90 534 398 14 67

#### REMOVED MEMBERS
- Dennis Kim: +90 529 408 33 70
- Larry Allen: +90 557 470 66 41
- Gerald Lee: +90 525 990 51 89
- Roy Smith: +90 518 569 97 25
- Paul Myers: +90 528 478 83 79


### KAYLA
#### ADDED MEMBERS
- Eric Green: +90 537 560 15 95
- Noah Martin: +90 511 538 44 22
- Eric Thomas: +90 528 722 48 63

#### REMOVED MEMBERS
- Joe Watson: +90 559 622 34 78


### WARNING: Members Belonging to Multiple Supervisors
- Henry Rivera: ETHAN, ZARA
- Benjamin Cook: KAYLA, ZARA
- Bruce Martinez: ELLIOT, FIONA
- George Young: ELLIOT, FIONA
- Brandon Moore: ELLIOT, FIONA
- Patrick James: ELLIOT, FIONA
- Noah Martin: FIONA, KAYLA
- Harold Myers: FIONA, KAYLA
- Joseph Alvarez: ELLIOT, LANA
- Jeremy Nelson: ELLIOT, LANA
- Alexander Thompson: ELLIOT, KAYLA
- Frank Diaz: ETHAN, KAYLA

Installation and Usage

Prerequisites

• Python 3.8 or higher
• Required libraries: Install them via pip:

  pip install pandas openpyxl

Usage

1. Clone the repository:

   git clone https://github.com/EForce11/excel-list-change-analyzer.git

2. Navigate to the project directory:

   cd excel-list-change-analyzer

3. Run the script with the example files:

   python analyzer.py

4. Replace file_before.xlsx and file_after.xlsx with your actual data files to analyze real data.

Contribution

Contributions are welcome! Feel free to open issues or submit pull requests to improve the project.

License

This project is licensed under the MIT License. See the LICENSE file for details.