Excel Dynamic Arrays - Excel Challenge Solution

Here I solve this challenge while introducing a number of powerful dynamic array functions

UNIQUE
XLOOKUP
SUMIFS
HSTACK
SORTBY
TAKE
DROP, REDUCE, VSTACK, LAMBDA
SEQUENCE
ROWS

📂Here's my solution file:

Check out the videos I mentioned:

2. The Spill Array explanation:

3. Autocorrect Magic

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[OzduSoleilDATA]

WYN! Thanks again for taking the challenge!
I like this solution--especially the way you handled the invalid choices. Rather than digging out the errant ones, you just applied a count the legit responses.

This is also really cool because the original solution got us an answer. But with some time and though, you got something more complex AND robust just in case there are more entries or, we need to use this again another time.

BRAVO!! 👏🏽👏🏽👏🏽👏🏽👏🏽

[mkcorrea]

wow what a hard challenge for me to apply you helpful 'weird' formula ! that's amazing, thanks for sharing!!

[IvanCortinas_ES]

Very good approach Wyn. Thanks for sharing.

[kkf]

Correct me if I'm wrong but shouldn't unavailable colors be removed and valid colors move up in the voting?
Example: Cactus Yellow isn't a valid choice
Petra Black Cactus Yellow Indigo
Shouldn't her votes be converted to
Petra Black Indigo
Where Indigo went from 1 point to 2 points.

[mohamedsoffar]

Sorry to tell you that, you used SUMIF, But I should be COUNTIF