We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

write an Excel formula to sort without blank rows

Ian Bell
Ian Bell asked
on
Medium Priority
310 Views
Last Modified: 2012-05-11
I would like a formula to list cell contents without blank rows
please see attached spreadsheet
Many thanks
Ian


 Excel-sort-Formula.xlsx
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you do this

1. click A
2. F5 - Special
3. Constants, OK
4. Ctrl + C
5. Click B1
6. Ctrl + V

:) we can save this a s macro I guess
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Consider the following array formula:
=INDEX(A$1:A$100,SMALL(IF(A$1:A$100<>"",ROW(A$1:A$100)-ROW(A$1)+1,""),ROWS(B$1:B1)))

To array-enter a formula, hold the Control and Shift keys down, then hit Enter. Excel should respond by adding curly braces { } surrounding your formula. If it does not, then select the cell, click in the formula bar and CTRL + Shift + Enter once again.

If your formula returns the same answer as you copy it down, make sure the workbook is in Automatic calculation mode.

Brad
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Note that the above array formula returns #NUM! error value if you exceed the number of non-blank items in your list. You can overcome that with:
=IFERROR(INDEX(A$1:A$100,SMALL(IF(A$1:A$100<>"",ROW(A$1:A$100)-ROW(A$1)+1,""),ROWS(B$1:B1))),"")
Still an array formula, so remember to CTRL + Shift + Enter

Brad
Ian Bellretired

Author

Commented:
Hi Byundt
Sorry for lengthy delay getting back to you. Been extremely busy.
Thank you kindly for the formula it works well
I would like to have same formula copied to adjoining column for "Sell"

Attached is the worksheet if you wouldn't mind taking a look
Many thanks Buy-Sell-2.xlsx
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Ian Bellretired

Author

Commented:
Excellent Job Brad
Thank you kindly

Just one question.
Why is it the formula does not work past 100 rows
after I change formula from 1:100 to say 1:150.? if doesn't seem to work
it displays blank cells

I need around max of 2000 rows
Ian Bellretired

Author

Commented:
how to change from 100 to 2000 rows. I tried the normal way by changing all instances of 100 to 2000 but blank cells were returned.

Cheers

Ian
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Ian,
Did you remember to Control + Shift + Enter?

I used this:
=IFERROR(INDEX(D$3:D$2000,SMALL(IF(D$3:D$2000<>"",ROW(D$3:D$2000)-ROW(D$3)+1,""),ROWS(C$1:C1))),"")

See attached workbook.

Brad
Buy-SellQ26963699.xlsx
Ian Bellretired

Author

Commented:
Seems like I can only copy formula down one row at a time otherwise if I try more than one I get a "you cannot change part of an array" message

Thanks Brad it all works well
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Ian,
Although completely unnecessary for your problem, it is possible to create an array formula and apply it to more than one cell at the same time. If you do, then you get the error message described when you try to change a formula within that range.

What you need to do is to clear the formula from the cells where it was array-entered as a block. Then put the formula in a single cell, Control + Shift + Enter, and copy it down as required.

Brad
Ian Bellretired

Author

Commented:
I'll give it a go
Many Thanks Brad
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.