Remove Blank Entries (No VBA) (2000)

Topic

New Reply

Can anyone think of a way to use only cell formulas (no macros, VBA, etc.) to reorder a list that has a list of names from cell A1:A10. Some of the cells will have a name in it, some will be blank. I want to remove, or at least move to the bottom, the blank cells so that all the names are listed first. Order of the names is unimportant.

EX:
Jeff
Jack
David
(blank)
(blank)
Matt

What I want is:
Jeff
Jack
David
Matt
(blank)
(blank)

Reply | Quote

Replies

I highlighted A1:A10, and did Data – Sort. The blanks are at the bottom…..and David appears before Jack…which doesn’t matter.

Reply | Quote

Thanks for the reply. I apologize that I wasn’t clear enough with what I was trying to accomplish. I would like to be able to create a formula(s) in a cell(s) to sort the list. I don’t want to use VBA, macros, or any built in function where an end user would have to do anything. I would like the data automatically updated when it is pulled from it’s source.

Reply | Quote

No built in functions…so the menu system is out.
It is supposed to be automatic, but without VBA….I think these are conflicting requirements. Without VBA, you lose the automatic part.
A formula in and of itself would get you halfway there, something along the lines of =IF(A1”,1,2) would go in B1. You’d still need some type of sort routine. As I wrote earlier, the formula isn’t necessary, but I can’t imagine the automatic magic without code.

We’ll see what others have to say…

Reply | Quote

You cannot use formulas to sort a range in place. You can, however, use formulas to create another range that contains the sorted data. See the attached workbook.

Reply | Quote

Hans… You’re a genius!! This is exactly what I want!

Legare, thanks for the VBA code. I’m sure it will come in handy in the future too!

Reply | Quote

As a couple of people have already told you, you can’t really do what you are asking without VBA. The attached workbook has a VBA Worksheet Change event routine that will automatically sort A2:A65536 if any cell in that range is changed. Try entering a name anywhere in column A, or deleting any name in column A and see what happens. I think this does exactly what you want, but it does use VBA.

Reply | Quote