Key Outcome: Developed a scalable group allocation system that combines Excel-based analysis with Python optimisation to generate efficient, compatibility-driven pairings.

Executive Summary

This project focused on developing a structured approach to grouping individuals based on compatibility constraints, while minimising manual decision-making.

Using Excel for initial data structuring and Python for optimisation, I built a system that:

• identifies overlap between existing and future group allocations,
• evaluates compatibility using personality-based rules,
• generates optimal pairings using an assignment algorithm.

The solution demonstrates how manual, cognitively intensive processes can be transformed into scalable, data-driven systems.

Business Problem

Allocating individuals into groups is often treated as a manual task, relying on:

• intuition,
• partial information,
• ad-hoc decision-making.

However, as the number of groups increases, this approach becomes:

• time-consuming,
• inconsistent,
• difficult to scale.

In this case, the problem involved:

• selecting individuals for upcoming groups,
• minimising disruption from previous allocations,
• ensuring compatibility between participants.

The key challenge was how to systematically assign people into groups while balancing overlap, compatibility, and scalability.

Methodology

The solution was built in three stages, moving from structured analysis to optimisation.

1. Group Overlap Analysis (Excel)

The first step was reducing the complexity of the problem. Instead of evaluating all possible group combinations, I:

• compared future groups against previously completed groups, and
• calculated the percentage of overlapping individuals

Using Excel functions such as MATCH, ARRAYFORMULA, and COUNTIF.

I identified which historical group required the least adjustment. This provided the logic and understanding that higher overlap is equivalent to fewer required decisions.

This step reduced the problem from evaluating all possibilities to focusing on the most relevant baseline.

2. Compatibility Modelling (Excel)

To improve grouping quality, I introduced a structured compatibility system using personality data. This involved:

• mapping individuals to personality types (MBTI),
• defining compatibility rules:
  • 1 → works well together,
  • -1 → does not work well,
  • 0 → neutral.

Using nested logic (VLOOKUP, SEARCH, IFERROR, LET), I created a full compatibility matrix between all individuals.

This allowed for:

• quick identification of strong and weak pairings, and
• removal of subjective decision-making

3. Optimisation Using Python

While Excel enabled analysis, pairing individuals optimally remained a combinatorial problem. To scale the solution, I implemented the Hungarian Algorithm (assignment optimisation) in Python:

• Converted compatibility scores into a cost matrix
• Penalised invalid/self pairings
• Solved for optimal pair assignments

This transformed the process from manual pairing mathematically optimal matching.

Skills

• Advanced Excel modelling (MATCH, ARRAYFORMULA, LET, INDEX/MATCH)
• Data structuring and transformation
• Constraint-based problem solving
• Optimisation techniques (assignment problem)
• Python (NumPy, SciPy) for scalable solutions
• Translating business rules into computational logic

Results & Insights

1. Manual grouping does not scale

Even with a small dataset, evaluating all pairing combinations becomes cognitively intensive.

2. Problem reduction is critical

By analysing overlap first, I reduced:

• decision complexity
• number of scenarios to evaluate

3. Compatibility can be quantified

Abstract concepts (like personality fit) can be translated into:

• structured rules
• numerical values
• decision matrices

4. Optimisation outperforms intuition

The Python-based solution:

• ensured consistent pairing logic
• avoided bias
• scaled efficiently with larger datasets

Business Recommendation

For organisations managing group allocation (training, events, teams):

• Structure the problem before solving it to reduce complexity through overlap analysis,
• Quantify decision criteria, including the conversion of subjective factors into measurable rules, and
• Automate optimisation, where possible, use algorithms for scalable, repeatable decisions.

Impact

This approach enables:

• Reduced manual effort in group allocation,
• More consistent and justifiable pairing decisions,
• Scalability from small groups to large datasets, and
• Improved quality of group dynamics.

More importantly, it shifts the process from intuition-driven to system-driven decision-making.

Next Steps

To extend this further:

I would:

• Introduce additional constraints like availability, skill levels, and preferences,
• Move from pair matching to multi-person group optimisation, and
• Develop an interface (dashboard or tool) for real-time grouping.