Sorting a column by using formula

  • Thread starter Thread starter Praise
  • Start date Start date
P

Praise

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
 
Praise said:
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"
Click to expand...

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)),""
 
Back
Top