Array Fomula: Last Data Row Not Fully Extracted

[Seeker After]

Good day All, I have an array formula in the uploaded spreadsheet to extract rows from the data table based on a single criterion from the drop-down. The trouble is that the last row of the data is only partially extracting, the first cell fills the remainder of the row errors out to blank. Any ideas would be great and Thanks.

 

[Luke M]

In your formula, you're changing the array sizes with INDIRECT. In there, it's using COUNTA to look at the columns. However, on your raw data sheet, only column A has data in row 1, so the results of this function vary from column to column in your output.

Change formula to:
=IFERROR(INDEX('RAW DATA'!A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!$A:$A)))),ROW(INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!$A:$A))),""),ROW()-2)),"")

Note, if you use Tables like in attached, formula is a bit simpler.

 

[Chihiro]

Because COUNTA('RAW DATA'!B:B) evaluates to 27...
So your formula only looks for range A3:A27.

Either use COUNTA('RAW DATA'!A:A) or add place holder value in B1.

 

[p45cal]

Enter anything into cells B1 and C1 of RAW DATA and you data will be complete.
To avoid having to do that, you're using COUNTA(B:B) and COUNTA(C:C) in your formulae in columns B and C, it would be better to use COUNTA($A:$A) as in change formula in cell A3 from:
=IFERROR(INDEX('RAW DATA'!A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!A:A)))),ROW(INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!A:A))),""),ROW()-2)),"")

to:
=IFERROR(INDEX('RAW DATA'!A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!$A:$A)))),ROW(INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!$A:$A))),""),ROW()-2)),"")

and copy down and across.

 

[Seeker After]

Thanks for the help everyone. It appears I may need to look at a lighter option, the array formulas, when applied in my actual spreadsheet (52 X 700), are a real drag on the processor. Perhaps I will begin a new line of internet research into VBA row extraction. Nothing ventured, nothing learned... Thanks Again.

 

[Chihiro]

If exploring VBA option. Lookup Advanced Filter.

It's well suited to this operation (or if you have access to PowerQuery, you can use that as well).

http://www.contextures.com/exceladvancedfiltervba.html
Check the related articles at the bottom of the link as well.

 

[Luke M]

Another formula approach

Rather than have each formula in your sheet calculate the array, have one column determine the row number needed to pull. (remove the INDEX portion, just need SMALL to return numbers) Let's say that's now in col A of your output sheet. Then, your other cells can be simply:

=INDEX('Raw Data'A:A, A3)

Now you can pull as many columns as you want w/o increasing calculation overhead.

 

[Seeker After]

Thanks all for chipping in. I would really like to avoid VBA if I can. Luke, I suppose I'm not quite understanding your advice. I assume that you intend the formula to be something like the one below, as an array? I tried it but seem to be getting nowhere. What am I missing?

=IFERROR(SMALL(IF(ISNUMBER(SEARCH($F$2,INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!$A:$A)))),ROW(INDIRECT("'RAW DATA'!$A3:$A"&COUNTA('RAW DATA'!$A:$A))),""),ROW()-2),"")

 

[p45cal]

…or a pivot table is quite fast and light. In the attached, two pivot tables (difference is only whether labels are repeated or not - delete the one you don't want) and a slicer.

 

[Luke M]

The INDIRECT functions are also killing you, since they're so VOLATILE. Here's the fleshed out version of my idea.

 

[r2c2]

@Seeker After

If you have Excel 2013 or above, you can use table level Slicers to get this effect. Just set up your raw data as a table and add slicer on Cert. Std column. I am not sure what is the purpose of such filtering (as you mentioned you have lots of data and even with filters you are bound to see several rows).

You may also consider using a helper column to fetch the index numbers of rows that need to be shown and then use INDEX() to fetch contents. This will be heaps faster than a one shot array formula. You can of course hide the helper column.

 

[Seeker After]

Thanks All. With your advice and complete the project. A special thanks, Luke your input was spot on!