Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
re-arrange data in date order
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??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
re-arrange data in date order
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. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "ereurts" wrote in message ... 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??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Data Order Reversal | Excel Discussion (Misc queries) | |||
data from one sheet to several in sequential order! | New Users to Excel | |||
unwanted number to date conversion while pasting data from web | Excel Worksheet Functions |