Remember Me?

#1
November 15th 04, 12:26 AM
 Josh Barbara Posts: n/a
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
November 15th 04, 01:55 AM
 Max Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Booger_Boy Charts and Charting in Excel 6 January 24th 05 02:41 PM Kim Excel Discussion (Misc queries) 1 December 28th 04 06:37 PM Molochi Excel Discussion (Misc queries) 6 December 22nd 04 08:13 PM Chuckak Excel Worksheet Functions 2 November 10th 04 06:04 PM nobrabbit Excel Worksheet Functions 1 November 7th 04 09:10 PM

All times are GMT +1. The time now is 10:05 AM.