# 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.

edit retag close merge delete

Sort by » oldest newest most voted

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.

Check the mark () to the left of the answer that solves your question.

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).

more

Hey LeroyG! You're a star! Thank you again.

( 2020-11-29 23:37:21 +0100 )edit

Try this trick

Or any of these tricks - look at YouTube

more

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 :-)

( 2018-09-13 11:31:50 +0100 )edit

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 :-)

( 2018-09-13 14:06:23 +0100 )edit

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! :-)

( 2018-09-13 15:54:37 +0100 )edit