Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Automatic Sorting of a group of columns.
Is it possible to setup an excel spreadsheet to automatically sort through
columns when data is being entered?. Or does the sort command need to be used every time i want to sort the columns by date? |
#2
|
|||
|
|||
One play to try ..
Assume data is entered in row2 down In Sheet1 ------------ Date Field1 Field2 01-Nov-04 Data1 Data11 02-Oct-04 Data2 Data12 03-Oct-04 Data3 Data13 04-Nov-04 Data4 Data14 02-Nov-04 Data5 Data15 etc Use an empty col to the right, say col K Put in K2: =IF(A2="","",A2+ROW()/10^10) Copy K2 down by as many rows as data is expected in the table, say down to K1000? In Sheet2 ------------- With the same headers in A1:C1 :Date Field1 Field2 Put in A2: =IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Shee t1!$K:$K,0)),"",OFFSET(She et1!$A$1,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1! $K:$K,0)-1,COLUMN(A1)-1)) Copy A2 across to C2, fill down by as many rows as was done in col K in Sheet1, i.e. down to C1000 Format col A as date Cols A to C will return the table from Sheet1 auto-sorted in ascending order by the dates in col A, (earliest dates first) viz. for the sample data above, it'll display as: Date Field1 Field2 02-Oct-04 Data2 Data12 03-Oct-04 Data3 Data13 01-Nov-04 Data1 Data11 02-Nov-04 Data5 Data15 04-Nov-04 Data4 Data14 -- And if you want it auto-sorted in descending order (i.e. latest dates on top), just replace SMALL with LARGE in the formulas, viz.: Put in A2 (in Sheet2): =IF(ISERROR(MATCH(LARGE(Sheet1!$K:$K,ROW(A1)),Shee t1!$K:$K,0)),"",OFFSET(She et1!$A$1,MATCH(LARGE(Sheet1!$K:$K,ROW(A1)),Sheet1! $K:$K,0)-1,COLUMN(A1)-1)) Copy across and down as before -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Josh Barbara" <Josh wrote in message ... Is it possible to setup an excel spreadsheet to automatically sort through columns when data is being entered?. Or does the sort command need to be used every time i want to sort the columns by date? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colors of columns after sorting data in the supporting table | Charts and Charting in Excel | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
copy group of cells to another group of cells using "IF" in third | Excel Worksheet Functions | |||
Group by count formula | Excel Worksheet Functions |