Sorting a column by using formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Praise - 22 Oct 2004 08:35 GMT

I am trying to use sort function just to delete blank cells in between
Sort order doesn't matter actually.

Data is coming by the use of simple cell reference of "another sheet

Reply to this Message

Frank Kabel - 22 Oct 2004 08:55 GMT

Hi
you may use the following addin to filter out blank cells:
http://www.rondebruin.nl/easyfilter.htm

Signature

Regards
Frank Kabel
Frankfurt, Germany

> I am trying to use sort function just to delete blank cells in
> between. Sort order doesn't matter actually.
>
> Data is coming by the use of simple cell reference of "another sheet"

Reply to this Message

Aladin Akyurek - 23 Oct 2004 11:09 GMT

Praise Wrote:

> I am trying to use sort function just to delete blank cells in between
> Sort order doesn't matter actually.
>
> Data is coming by the use of simple cell reference of "another sheet"

Your sample:

Cell A1: Mr. abc
Cell A2: Mr. XYZ
Cell A3: Blank Cell
Cell A4: Blank Cell
Cell A5: Ms. Lee
Cell A6: Mr. PQR

Let the sheet named Source house the above sample.

Insert 2 new rows before the current row 1 such that the sample wil
occupy:

A3:A8

In A2 enter the label: Data.

In B1 enter: 0, which is required.

In B2 enter the label: Coding

In B3 enter & copy down:

=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")

Now switch to the destination sheet. Lets call this sheet Destination.

On Destionation, do the following...

In A1 enter:

=LOOKUP(9.99999999999999E+307,Source!B3:B8)

In A2 enter the label New Data.

In A3 enter & copy down as far as needed:

=IF(ROW()-ROW(A$3)+1<=$A$1,INDEX(Source!$A$3:$A$8,MATCH(ROW()-ROW(A$3)+1,Source!$B$3:$B$8)),""

Reply to this Message