The Curtain Problem. The Lists Solution.
How many values fit in a cell?
Until now, one. And that one rule shapes more of your spreadsheet than you’d think.
I’m currently building a pricing tool for a curtain and upholstery manufacturer. To state the obvious, most curtains cover a single window and open one way (from the center, from the left, or from the right). One width. One draw direction. Simple.
But that’s not always the case. Think of a bay window. You need a curtain for the main window and one for each side angle. Each of those panels has its own width and its own draw direction. However, they’re all considered part of the same curtain from an order standpoint.
How often will these show up? I don’t know. And it doesn’t matter. It’s an option they need, so it’s an option I build. But accommodating this special case takes a lot of effort. Here’s what it requires:
- Macros to add two rows for each extra panel: one for its width, one for its draw direction.
- TEXTJOINs to squeeze all of it into a single cell. A bay window with a 48″ center window flanked by two 20″ windows becomes 48:20:20|Center:Right:Left.
- Extra steps in every lambda to pull that text back apart into individual widths and directions before a single calculation can run.
That’s a lot of hoops. For one type of curtain.
Enter lists and arrays
Microsoft just released lists and arrays in cells. One cell can now hold multiple values, and Excel treats each one as its own value. The widths? One list. The draw directions? Another. No extra rows. No concatenating. No parsing. It does in one step — natively — what took me three.
I can’t use it in production yet; it’s still in beta. I’m also nearly done with this project. But I can see exactly where it would have gone . . . and where it will go next time.
And that’s one simple example.
Changing the whole approach . . . again
A year from now, I don’t know what my spreadsheets will look like. I do know they’ll look different. Every major Excel release changes how I build, and this one may change it more than most.
I’m certain of one thing: this release only strengthens my lambda-first approach. My lambdas already pass arrays from one step to the next. Now the data can arrive already shaped that way.
Christopher
Another significant shift in Excel development. The days of Index/Match are long gone. We are in the times of PQ and LAMBDA.