Sort array depending on 2nd column

[pmguerra]

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!

[NBVC]

Try:

Please Login or Register  to view this content.

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

[pmguerra]

Thank you for your help, NBVC!

However the array is not sorted, it just eliminates the blank cells. For example, the values of rows 47 and 48 should be the first ones to appear.

That's why I used INDEX, MATCH, SMALL and so on, trying to follow other examples of sorting.

[NBVC]

Add a helper column in C2:

Please Login or Register  to view this content.

copied down

Then in E2:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copy down.

[pmguerra]

ok! That did the trick...

One more question... How to avoid blank cells from being sorted?

[NBVC]

You mean the opposite of what I just did?

[pmguerra]

of course not! Let me explain

In your first post you helped me with a formula that eliminated blanks but did not sort the array.

In the second post you gave me a formula which sorted the array but does not eliminate blanks.

What I asked you just now is how to get a SORTED array WITHOUT blanks.

thank you again for your help!

[NBVC]

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

No??

[pmguerra]

Ok, I know what you mean. Yes I used you formula and it worked perfectly.

However the original data isn't all neat and clean like this. I didn't explain this to you, my bad.

Try erasing any value in column A and you'll understand what I'm saying.

[NBVC]

Change C2 formula to:

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

copied down.

[pmguerra]

hmmmm....

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

I don't understand why...

[NBVC]

Another revisiion required to meet your last needs...

Formula in E2 changed slightly to:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[NBVC]

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

[pmguerra]

Definitely I can't get it to work...

I'm sending the file attached. Can you take a look?

Thank you for your help and patience...

[NBVC]

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

Please Login or Register  to view this content.

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

[pmguerra]

Yes, you are right, it should be blank.

I've corrected it and also the relative reference.

I now know why I get an #N/D error. If you change the ranges from 550 to 560 (filled with blank cells), you will also get 10 #N/D and then the correct values.

But why doesn't this happen for the other cells which are also blank?

[NBVC]

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.

[pmguerra]

Definitely not getting it right...

One more detail... When I add one row to the middle of the range, I get the #N/D error... Even in the example file you worked on...

I'll have to try and figure out if there's something wrong witth the data and work from there.

Thank you very much for you time, you've been a great help!

[NBVC]

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?

[pmguerra]

I'm retrieving the data from another wokbook.

Can this be causing the problem?

[NBVC]

It might... kind of depends on how you are retrieving the data, what is actually being returned (ie. real blanks or not, special chars, etc). Are some things manually entered and some automatically retrieved?

Are you getting #N/A error or #NUM error in english... I am not sure what #N/D translates to.. And is it only showing up at bottom of list? If so, then there is a counting problem somewhere.. either in the formula in column D or the formula in F2.

It would be easiest to see a most realistic sample of what you are working with.

[pmguerra]

Sorry about the english:

#N/D translates to #N/A and it appears at the top of the sorted range.

I'll prepare you a sample of this workbook and attach it.

Thank you

[pmguerra]

SOLVED!!!

The problem was with the helper column which had a wrong range...

Thank you very much for your help!

I was getting desperate...

MANY THANKS!!!