jeffreyweir
Active Member
Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.
Okay, people. We’ve got the following dataset:
[pre]
[/pre]
…which you could also represent like this:
{1,2,3,"a","b","c";4,5,6,"d","e","f"}
Give this data the named range a (a for array).
Your challenge is to dynamically turn this 2D array to a 1D array that can then be used directly by other formulas, without being entered over a range.
So your formula will evaluate to this:
{1,2,3,"a","b","c",4,5,6,"d","e","f"}
As per usual, there are some rules:
This is a one-formula challenge. No helper cells, no VBA, no performance enhancing steroids/addins.
Your time starts……NOW()
Okay, people. We’ve got the following dataset:
[pre]
Code:
1 2 3 a b c
4 5 6 d e f
…which you could also represent like this:
{1,2,3,"a","b","c";4,5,6,"d","e","f"}
Give this data the named range a (a for array).
Your challenge is to dynamically turn this 2D array to a 1D array that can then be used directly by other formulas, without being entered over a range.
So your formula will evaluate to this:
{1,2,3,"a","b","c",4,5,6,"d","e","f"}
As per usual, there are some rules:
This is a one-formula challenge. No helper cells, no VBA, no performance enhancing steroids/addins.
Your time starts……NOW()