Automatic Sorting of a group of columns.

[Guest]

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?

 

[Max]

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 ate Field1 Field2

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$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)),Sheet1!$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