Ask Your Question

Summary from sheet with blank rows

asked 2018-09-12 20:49:49 +0100

jamesb gravatar image


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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-11-29 21:40:51 +0100

LeroyG gravatar image

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

edit flag offensive delete link more


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

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

answered 2018-09-13 07:07:44 +0100

JohnSUN gravatar image

Try this trick

Using Filter.gif

Or any of these tricks - look at YouTube

edit flag offensive delete link 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:


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

jamesb gravatar imagejamesb ( 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 :-)

JohnSUN gravatar imageJohnSUN ( 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! :-)

jamesb gravatar imagejamesb ( 2018-09-13 15:54:37 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-12 20:49:49 +0100

Seen: 66 times

Last updated: Nov 29 '20