re-arrange data in date order

[Guest]

Hi,

My name is Erik, I am trying to duplicate data in a worsheet in date order.
I know about the sort function and that's not what I am after. I need the
data to be duplicated in a new row and sorted by date. Can this be done? I
have been thinking about a nested "if" statement but the data row is lenghtly
and this will be very labour intencive..
Any suggestions???

 

[Max]

One play to try ..

Sample construct available at:
http://www.savefile.com/files/6345502
AutoSort_DateOrder_ereurts_wks.xls

In Sheet1, source data is in cols A to C,
data from row2 down with dates in col A, eg:

Date Field1 Field2
18-Dec-05 Data1 Data1
06-Dec-05 Data2 Data2
18-Dec-05 Data3 Data3
06-Dec-05 Data4 Data4
07-Dec-05 Data5 Data5
etc

(Possibility of duplicate dates in col A is assumed)

In a new sheet: Ascending,
With the same col headers pasted in A1:C1

Put in E2:
=IF(Sheet1!A2="","",Sheet1!A2+ROW()/10^10)
Copy down to say, E100, to cover the max expected extent of data in Sheet1.
(Leave E1 empty)

Put in A2:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)))
Copy A2 across to C2, fill down to C100
(cover the same extent as in col E)

Format col A as dates. Sheet2 will return an ascending sort of Sheet1 by the
dates in col A. Lines with duplicate dates, if any, will appear in the same
relative order as in Sheet1
--------

And if we want it auto-sorted in *descending* order instead
just slightly amend the formulas in E2 and A2 ...

In a new sheet: Descending
With the same col headers pasted in A1:C1

Put in E2:
=IF(Sheet1!A2="","",Sheet1!A2-ROW()/10^10)

( the change is from "+" to "-" for the part: "ROW()/10^10" )

Copy down to say, E100, to cover the max expected extent of data in Sheet1.
(Leave E1 empty)

Put in A2:
=IF(ISERROR(LARGE($E:$E,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(LARGE($E:$E,ROW(A1)),$E:$E,0)))

( the change is to replace SMALL with LARGE )

Copy A2 across to C2, fill down to C100
(cover the same extent as in col E)

Format col A as dates. Sheet2 will return a descending sort of Sheet1 by the
dates in col A. Lines with duplicate dates, if any, will appear in the same
relative order as in Sheet1.