Auto Generate Sorting table(empty cell)??
 

Previous I try the formula below, but NOT WORKING IF COLUMN HAVE EMPTY CELL..
Please help!!

SHEET 1
colA colB
order1 3
order2
order3 2
order4
order5 1
order6

SHEET 2 (sort by colB)
colA colB
order5 1
order3 2
order1 3
order2
order4
order6




In Sheet2,

Put in A1:
=IF(ISERROR(SMALL($C:$C,ROW())),"",IF(INDEX(Sheet1 !A:A,MATCH(SMALL($C:$C,ROW()
),$C:$C,0))=0,"",INDEX(Sheet1!A:A,MATCH(SMALL($C:$ C,ROW()),$C:$C,0))))

Copy A1 to B1

Put in C1:
=IF(AND(Sheet1!A1="",Sheet1!B1=""),"",IF(AND(Sheet 1!A1<"",Sheet1!B1=""),
10^10+ROW(),Sheet1!B1+ROW()/10^10))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

Auto Generate Sorting table(empty cell)??
 

You probably have invisible whitespaces in col B, which TRIM can handle

Just replace the formula in C1 with this:
=IF(AND(Sheet1!A1="",TRIM(Sheet1!B1)=""),"",IF(AND (Sheet1!A1<"",TRIM(Sheet1!B1)=""),10^10+ROW(),She et1!B1+ROW()/10^10))
Copy C1 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"daniellchiu via OfficeKB.com" <u36625@uwe wrote in message
news:79de3554ef1fb@uwe...
Previous I try the formula below, but NOT WORKING IF COLUMN HAVE EMPTY
CELL..
Please help!!

SHEET 1
colA colB
order1 3
order2
order3 2
order4
order5 1
order6

SHEET 2 (sort by colB)
colA colB
order5 1
order3 2
order1 3
order2
order4
order6




In Sheet2,

Put in A1:
=IF(ISERROR(SMALL($C:$C,ROW())),"",IF(INDEX(Sheet1 !A:A,MATCH(SMALL($C:$C,ROW()
),$C:$C,0))=0,"",INDEX(Sheet1!A:A,MATCH(SMALL($C:$ C,ROW()),$C:$C,0))))

Copy A1 to B1

Put in C1:
=IF(AND(Sheet1!A1="",Sheet1!B1=""),"",IF(AND(Sheet 1!A1<"",Sheet1!B1=""),
10^10+ROW(),Sheet1!B1+ROW()/10^10))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1