# Need a formula that doesn't leave blank rows

1. ## Need a formula that doesn't leave blank rows

Good morning Forum,

Can anyone help me with a formula, if possible, that would leave no blank rows in my "result" section, as shown below.

Capture.PNG

I know it is possible if I keep the itermediate column, with this array formula

``Please Login or Register  to view this content.``
but I really need to get rid of that column.

2. ## Re: Need a formula that doesn't leave blank rows

Which are you classing as the intermediate column???

3. ## Re: Need a formula that doesn't leave blank rows

Hello Ali Sorry, the one with the blank rows, that means range from B13 to B19 in the attached file.

4. ## Re: Need a formula that doesn't leave blank rows

``Please Login or Register  to view this content.``

5. ## Re: Need a formula that doesn't leave blank rows

Sybille, try the following in B13 and pul down as needed. Look at the description of AGGREGATE in excel help.
=IFERROR(INDEX(B\$3:B9,AGGREGATE(15,6,(ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1)/(A3:A9=""),ROWS(\$B\$3:B3))),"")

6. ## Re: Need a formula that doesn't leave blank rows

Thank you wyowhite and protonLeah!

wyo, your formula works but gives the last row twice.

Ben, works great!

Now I just have to investigate both your suggestions to add them to my knowledge book

Have a good day.

7. ## Re: Need a formula that doesn't leave blank rows

Ben, I am trying to understand the formula. There is just one thing that bugs me: "...ROW(A\$1:A\$7)...".
Why this range?
Thank you again.

8. ## Re: Need a formula that doesn't leave blank rows

You want to return values from an array spanning rows B3 - B9; but, they are elements 1 - 7 of the array to be used by the INDEX function (rows are off by 2).

The IF function looks at A3-A9 testing for blanks (i.e., no "x") that evaluates to: {True;True;True;False;True;False;True}. You want to map to the elements of the array.
If you use Row(A3:A9) it results in the array {3;4;5;6;7;8;9} which is ANDed with the IF test to produce {3;4;5;FALSE;TRUE;7;FALSE;9}. This would be passed to SMALL and result in a list starting with Client3, but 8 & 9 would produce errors because they are beyond the array.

So, you could use:
IF(\$A\$3:\$A\$9<>"x",ROW(\$A\$3:\$A\$9)-2)
or
IF(\$A\$3:\$A\$9<>"x",ROW(\$A\$1:\$A\$7))
or even:
IF(\$A\$3:\$A\$9<>"x",{1;2;3;4;5;6;7})

9. ## Re: Need a formula that doesn't leave blank rows

Wow! I get it now. Thank you Ben

10. ## Re: Need a formula that doesn't leave blank rows

My Bad. the array for the comparison needs to be absolute here is the correction.
=IFERROR(INDEX(B\$3:B9,AGGREGATE(15,6,(ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1)/(\$A\$3:\$A\$9=""),ROWS(\$B\$3:B3))),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1