write an Excel formula to sort without blank rows

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

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

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

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

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

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

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

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

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

I'll give it a go
Many Thanks Brad