# Summary from sheet with blank rows

Hi

I have a sheet which produces results something like this:

I’d like to display these results in sequential rows in another sheet. I’m a little stumped, but feel sure that there is some way of making an array match to do this. I’d be most grateful for assistance.

Many thanks.

Try this trick

Or any of these tricks - look at YouTube

Hi JohnSUN

Thank you kindly for your reply. I’m most grateful. Although this method works for non-calculated data, it doesn’t seem to work for calculated data. In my sheet, the first cell of the first row shown contains the following formula:

=IF(L2=1,F2,"")

All the other cells contain the identical formula, relative to their position in the sheet. I think I need to show the filtered results in another sheet by some form of array search. Does anyone have another idea?

Thanks again

And what other value in column L? Zero or empty cell? In this case, you can use this column as filter criteria and put to report values from column F

Hi JohnSUN. Thanks again. As I said, all cells contain the identical formula, relative to their position in the sheet. No cell is raw data, every cell is a formula. This method only seems to work on raw data.

I’m hoping either I’ll figure out the array match method myself, or some kind person will tell me and put me out of my misery!

Thinking that original data is in Sheet1.A1:B22 (or A:B complete columns), and summary from Sheet2.A1:B1 down.

Paste in Sheet2.A1: `=IFERROR(SMALL(Sheet1.\$A\$1:\$A\$22;ROW());"--")` (or `=IFERROR(SMALL(Sheet1.A:A;ROW());"--")`)

Paste in Sheet2.B1: `=IFERROR(VLOOKUP(A1;Sheet1.\$A\$1:\$B\$22;2;0);"--")` (or `=IFERROR(VLOOKUP(A1;Sheet1.A:B;2;0);"--")`)

Fill down as needed.

See sample file.

More LibreOffice Help on ROW, SMALL, VLOOKUP, IFERROR.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.