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
but I really need to get rid of that column.
Please Login or Register to view this content.
Thank you in advance!
Which are you classing as the intermediate column???
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hello AliSorry, the one with the blank rows, that means range from B13 to B19 in the attached file.
Please Login or Register to view this content.
Ben Van Johnson
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))),"")
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.
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.
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)orIF($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})
Wow! I get it now. Thank you Ben
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks