write an Excel formula to sort without blank rows

I would like a formula to list cell contents without blank rows
please see attached spreadsheet
Many thanks
Ian


 Excel-sort-Formula.xlsx
raceproretiredAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
When you applied the formula, the range in your IF test spanned three columns. It should only apply to a single column. That's why you were having trouble with column I.

I changed your column H formula to:
=IFERROR(INDEX(D$3:D$100,SMALL(IF(D$3:D$100<>"",ROW(D$3:D$100)-ROW(D$3)+1,""),ROWS(C$1:C1))),"")

Your column I formula then becomes:
=IFERROR(INDEX(F$3:F$100,SMALL(IF(F$3:F$100<>"",ROW(F$3:F$100)-ROW(F$3)+1,""),ROWS(E$1:E1))),"")

Brad
Buy-SellQ26963699.xlsx
0
 
HainKurtSr. System AnalystCommented:
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
0
 
byundtCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
byundtCommented:
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
0
 
raceproretiredAuthor 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
0
 
raceproretiredAuthor 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
0
 
raceproretiredAuthor 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
0
 
byundtCommented:
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
0
 
raceproretiredAuthor 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
0
 
byundtCommented:
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
0
 
raceproretiredAuthor Commented:
I'll give it a go
Many Thanks Brad
0
All Courses

From novice to tech pro — start learning today.