Sort array depending on 2nd column

Hi!

I would like to sort one array (1 column) based on another column.

Column 1 is the column to be sorted but I only want to sort if column B is blank ("").

I can't get my formula to work.

Please check the attached workbook.

Thank you for your help!

Try:

=IF(ROWS($A$1:$A1)>COUNTBLANK($B$2:$B$100),"",INDEX($A$2:$A$100,SMALL(IF($B$2:$B$100="",ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down as far as you want to retrieve all data.

Add a helper column in C2:

=COUNTIF($A$2:$A$100,"<"&A2)

copied down

Then in E2:

=IF(ROWS($A$1:$A1)>COUNTBLANK($B$2:$B$100),"",INDEX($A$2:$A$100,MATCH(SMALL(IF($B$2:$B$100="",$C$2:$C$100),ROWS($A$1:$A1)),$C$2:$C$100,0)))

confirmed with CTRL+SHIFT+ENTER and copy down.

You mean the opposite of what I just did?

I think that is what I gave you last....

No??

Change C2 formula to:

=IF(A2="","",COUNTIF($A$2:$A$100,"<"&A2))

copied down.

Originally Posted by pmguerra

hmmmm....

It works but it returns #N/D for the first 9 cells of the array...

I don't understand why...

make sure your cell ranges match your data...

Post a more indicative example showing the new problem.

Also, see my last added post....

You have somehow made C2 in the IF() logical test within the SMALL() function relative... it should be Absolute:

=IF(ROWS($A$1:$A1)>$F$2,"",INDEX($B$2:$B$550,MATCH(SMALL(IF($C$2:$C$550<>"",$D$2:$D$550),ROWS($A$1:$A1)),$D$2:$D$550,0)))

Also, it looks as though you have changed the requirement to show items where column C is not blank.. originally you said where C is blank.

If that is the case, then change change F2 to: =COUNTA(C:C)-1

Another revisiion required to meet your last needs...

Formula in E2 changed slightly to:

=IF(ROWS($A$1:$A1)>SUMPRODUCT(--($A$2:$A$100<>""),--($B$2:$B$100="")),"",INDEX($A$2:$A$100,MATCH(SMALL(IF($B$2:$B$100="",$C$2:$C$100),ROWS($A$1:$A1)),$C$2:$C$100,0)))

confirmed with CSE and copied down

or to reduce calcs... add =SUMPRODUCT(--($A$2:$A$100<>""),--($B$2:$B$100="")) to a blank cell, like D2, then formula in E2:

=IF(ROWS($A$1:$A1)>$D$2,"",INDEX($A$2:$A$100,MATCH(SMALL(IF($B$2:$B$100="",$C$2:$C$100),ROWS($A$1:$A1)),$C$2:$C$100,0)))

Not sure what you mean.. rows 550 to 560 are already blank.

Also, if you are identifying the blank column C values, then revert back to the Sumproduct formula in F2 to identify number of entries.

I just inserted a row (with details) into the last file I attached... and I get no errors. I only get a blank row in the results because of inserted row.. just copy formula down from above and all is okay.

Can you post this situation?