How do you extract a subarray from an array in a worksheet function?

Question

Is there some way of getting an array in Excel of a smaller size than a starting array in a cell worksheet function?

So if I had:

{23, "", 34, 46, "", "16"}

I'd end up with:

{23, 34, 46, 16}

which I could then manipulate with some other function.

Conclusion: If I was to do a lot of these I would definitely use jtolle's UDF comb solution. The formula that PPC uses is close, but diving in and testing, I found it gives errors in the empty slots, misses the first value, and there is an easier way to get the row numbers, so here is my final solution:

=IFERROR(INDEX($A$1:$A$6, SMALL(IF(($A$1:$A$6<>""),ROW($A$1:$A$6)),ROW(1:6))),"")

Which must be entered as an array formula (CTRL-SHIFT-ENTER). If being displayed then it must be entered in at least an area as big as the resultset to show all results.

Answer 1

If all you want to do is grab a subset of an array, and you already know the positions of the elements you want, you can just use INDEX with an array for the index argument. That is:

=INDEX({11,22,33,44,55},{2,3,5})

returns {22,33,55}. But that's usually not very useful because you don't know the positions, and I don't know any way to get them without a UDF.

What I have done for this kind of in-worksheet array filtration is to write a UDF with the following form:

'Filters an input sequence based on a second "comb" sequence.
'Non-False-equivalent, non-error values in the comb represent the positions of elements
'to be kept.
Public Function combSeq(seqToComb, seqOfCombValues)

    'various library calls to work with 1xn or nx1 arrays or ranges as well as 1-D arrays

    'iterate the "comb" and collect positions of keeper elements

    'create a new array of the right length and copy in the keeper elements

End Function

I only posted pseudocode because my actual code is all calls to library functions, including the collect-positions and copy-from-positions operations. It would probably obscure the basic idea, which is pretty simple.

You'd call such a UDF like so:

=combSeq({23, "", 34, 46, "", "16"}, {23, "", 34, 46, "", "16"} <> "")

or

=combSeq(Q1:Q42, SIN(Z1:Z42) > 0.5)

and use Excel's normal array mechanics to generate the "comb". It's a lightweight, Excel-friendly way to get a lot of the benefits of the more standard filter(list-to-filter, test-function) function you might see in other programming systems.

I use the name "comb" because "filter" usually means "filter with this function", and with Excel you have to apply the test function before calling the filtration function. Also it can be useful to compute one "comb" as an intermediate result and then use it to...er, comb...multiple lists.

Answer 2

There is an answer on this site: http://www.mrexcel.com/forum/showthread.php?t=112002. Not much explanation though.

Assuming you have data with blank cells on column A and you put this in column B; that will retrieve data in the same order skipping the blanks

=INDEX(  $A$1:$A$6, 
         SMALL(  
            IF(
               ($A$2:$A$6<>""), 
               ROW($A$2:$A$6)
            ), 
         ROW()-ROW($B$1)
         )
      )

Here is the explanation:

• ROW()-ROW($B$1) is just a trick that will give you an incrementing number (ie 1 in B1, 2 in B2...)
• IF (... , ROW($A$2:$A$6) ) is the main part of the trick: it builds an array of the row numbers where the IF condition is true (note that the IF has no 'else' value)
• SMALL(..) will return the Xth smallest value of that array (in our case the number of the Xth nonblank row), where X is the row number of the current cell (1 in B1 ...)
• INDEX will then translate from the row number to its value
• Note that INDEX and ROW start one row above the actual table to always have an offset > 0 (INDEX does not like zeros)