Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 15th 04, 12:26 AM
Josh Barbara
 
Posts: n/a
Default 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   Report Post  
Old November 15th 04, 01:55 AM
Max
 
Posts: n/a
Default

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?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Colors of columns after sorting data in the supporting table Booger_Boy Charts and Charting in Excel 6 January 24th 05 02:41 PM
Columns in Excel will not allow user to click in them Kim Excel Discussion (Misc queries) 1 December 28th 04 06:37 PM
Counting the Contents of Two Columns Molochi Excel Discussion (Misc queries) 6 December 22nd 04 08:13 PM
copy group of cells to another group of cells using "IF" in third Chuckak Excel Worksheet Functions 2 November 10th 04 06:04 PM
Group by count formula nobrabbit Excel Worksheet Functions 1 November 7th 04 09:10 PM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017