There's a quiet frustration in the question itself: "I know I must be missing something super simple." It's the kind of moment that makes capable spreadsheet users doubt their own instincts. But the real issue isn't a lack of skill. It's that the tool's most obvious function, `=unique`, was designed for single columns or tidy ranges, not for the messy reality of IDs scattered across a table where every row and column might hold a repeat. When the data isn't shaped the way the function expects, the function doesn't fail loudly. It just returns something that looks close, but isn't right. That's worse than an error message.
What this user actually needs is a way to gather every ID from every relevant range, then collapse that combined list into a single column of distinct values. The standard approach is to stack the columns first, using something like `={A2:A; B2:B; C2:C}` to create one continuous vertical array. Once that's done, wrapping the whole thing in `=unique(...)` gives you exactly what you asked for: every ID that appears anywhere, listed once. No table formatting, no duplicate rows. It's a two-step solution that feels like a secret handshake the first time you see it, but it's the kind of pattern that turns a frustrating session into a small victory. The fix isn't exotic or flashy. It's just the right sequence of functions for the structure of the data.
What stands out here is how often this scenario plays out across real workflows. People aren't building spreadsheets for the sake of it. They're reconciling accounts, merging imports, or auditing logs where the same identifier legitimately shows up in multiple places. The goal isn't to clean the data for its own sake. It's to get an accurate answer so they can move on to the next task with confidence. When a tool makes that harder than it needs to be, the instinct is to blame yourself. But the better response is to look for a more fitting method, and that's exactly what this thread provides.
So if you've ever stared at a `=unique` result and wondered why it still had duplicates, this is your answer. Combine the ranges first, then deduplicate. It's simple once you see it, and it works across any number of columns. The next time a spreadsheet hands you a list that's almost right, remember that the solution might just be one extra step away. Not because you missed something obvious, but because the right move wasn't the first one the interface suggested.