Summary from sheet with blank rows


I have a sheet which produces results something like this:

image description

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

Using Filter.gif

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:


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

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

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

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 (x86); OS: Windows 6.1.

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information.

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

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

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