Combining two uneven prospect rankings shouldn't require a statistics degree. Yet here we are, watching a frustrated baseball fan try to merge a list of 600 players with a list of 500, only to find that hundreds of names appear on just one side. The problem is real, and it's not about baseball. It's about what happens when your data refuses to cooperate with your tools.
The core issue is structural. A simple average based on position works only when both lists contain the same players. When one scout ranks a player 115th and the other doesn't rank him at all, you can't just drop that player into a formula. The missing data point isn't a zero. It's a blank. And a blank tells you nothing about where that player should land. You need a method that accounts for partial overlap, not one that pretends the problem doesn't exist.
What this user needs is a way to treat each list as its own scoring system, then normalize those scores onto a shared scale. Convert rank into a percentile based on the total number of players in each list. A player ranked 115th in a list of 600 sits at roughly the 81st percentile. The same rank in a list of 500 lands at the 77th percentile. Now you have comparable values. Players who appear on only one list get a percentile from that list and no penalty for the missing one. Average the percentiles where both exist. Use the single percentile where only one exists. Sort the result. That gives you a merged ranking that respects the original opinions without forcing false averages.
This is exactly the kind of problem that a smarter spreadsheet can solve in seconds. Traditional tools force you to build workarounds, write conditional formulas, or manually flag missing entries. An AI-native approach can recognize the uneven structure, apply the normalization logic, and produce the merged list without you having to think about percentiles at all. The data tells the story. The tool should handle the math.
So here is the practical takeaway: if you are manually aligning two uneven lists, you are doing work the machine should do. The solution exists. It just requires a tool that treats data as a conversation, not a grid.