How can I generate Sorting table automatic from sheet 1 to Sheet 2?? cause
Date in sheet 1 always change
Sheet 1
A B
1 3
2
3 2
4 1
5 1

Sheet 2
A B
4 1
5 1
3 2
1 3
2

Thanks!!

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

One thought ..

Assuming source data in Sheet1, cols A and B, from row1 down (data in col B
is assumed to be only numbers)

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))

Select A1:C1, copy down to cover the max expected extent of source data in
Sheet1, down to say row500? Hide away col C. Cols A and B will return the
results that you seek, all neatly bunched at the top. Results returned will
be dynamic to data changes in Sheet1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"daniellchiu via OfficeKB.com" <u36625@uwe wrote in message
news:79d1e00ded5cd@uwe...
How can I generate Sorting table automatic from sheet 1 to Sheet 2?? cause
Date in sheet 1 always change
Sheet 1
A B
1 3
2
3 2
4 1
5 1

Sheet 2
A B
4 1
5 1
3 2
1 3
2

Thanks!!

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

Thanks!! it work but i also got 1 problem-
cause Sheet 1 col B data may contain empty cell, then I can't generate the
table.

P.S. Col A data wont be change, and always contain values.



Max wrote:
One thought ..

Assuming source data in Sheet1, cols A and B, from row1 down (data in col B
is assumed to be only numbers)

In Sheet2,

Put in A1:
=IF(ISERROR(SMALL($C:$C,ROW())),"",IF(INDEX(Sheet 1!A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0))=0,"",INDE X(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(Shee t1!A1<"",Sheet1!B1=""),10^10+ROW(),Sheet1!B1+ROW( )/10^10))

Select A1:C1, copy down to cover the max expected extent of source data in
Sheet1, down to say row500? Hide away col C. Cols A and B will return the
results that you seek, all neatly bunched at the top. Results returned will
be dynamic to data changes in Sheet1.
How can I generate Sorting table automatic from sheet 1 to Sheet 2?? cause
Date in sheet 1 always change
[quoted text clipped - 15 lines]

Thanks!!

--
Message posted via http://www.officekb.com

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:79db2161f0e03@uwe...
Thanks!! it work but i also got 1 problem-
cause Sheet 1 col B data may contain empty cell, then I can't generate the
table.

P.S. Col A data wont be change, and always contain values.