Question about ordering columns based on date

  • Thread starter Thread starter Schizoid Man
  • Start date Start date
S

Schizoid Man

Hi,

I have the following table and a slight ordering problem. Each of the rows
A, B, C, etc has data associated with 5 fixed dates - from 20-Dec-09 to
20-Dec-18. In addition there is one "floating" column added to the end. In
this example, it is the data for column 20-Dec-10.

20-Dec-09 20-Dec-11 20-Dec-13 20-Dec-15 20-Dec-18 20-Dec-10
A 84 49 88 91 26 82
B 9 68 39 89 50 59
C 45 5 46 69 41 38
D 35 28 70 69 47 59

The dates for the "floating" column always change - it could be any date
before, after or in between the five fixed dates. I'd like to sort this data
so that the columns are ordered by dates in increasing order from left to
right.

Any suggestions?

Thanks.
 
Hi Schizzo,

If I am reading it right all you need is Data>Sort

Select all of your data and go to Data>Sort
Click the Options button and check 'Sort Left to Right' and OK
Then from the dropdown select the row no. and check ascending.

That's for pre 2007.

If you are using 2007 then I can't help other than to say that
the process will probably very similar but at the same time
completely different ;-)

HTH
Martin
 
MartinW said:
Hi Schizzo,

If I am reading it right all you need is Data>Sort

Select all of your data and go to Data>Sort
Click the Options button and check 'Sort Left to Right' and OK
Then from the dropdown select the row no. and check ascending.

That's for pre 2007.

If you are using 2007 then I can't help other than to say that
the process will probably very similar but at the same time
completely different ;-)

HTH
Martin
Click to expand...

Hi Martin,

I need to perform the actual sorting itself on another worksheet, so my
preferred solution would be to use a combination of worksheet functions.

Thanks.
 
See Bernd's answer to your post in the General Questions group
and please DON"T multipost, it is very frustrating to spend time on
a problem in one group and then find that someone else has
already provided a solution in another group.

Most responders in the group monitor all of the groups, so it
matters very little where you post, you will get the same response.

Regards
Martin
 
On the other sheet, use a formula like this in cell A1:

=INDEX('Data Sheet'!$A$1:$E$10000,ROW(),MATCH(SMALL('Data
Sheet'!$A$1:$E$1,COLUMN()),'Data Sheet'!$A$1:$E$1,FALSE))

Copy to cells B1:E1, then down for as many rows as you have data on your
data sheet (named, for this example, "Data Sheet")

This assumes that your dates are in row 1 of sheet Data Sheet....

HTH,
Bernie
MS Excel MVP
 
Bernie Deitrick said:
On the other sheet, use a formula like this in cell A1:

=INDEX('Data Sheet'!$A$1:$E$10000,ROW(),MATCH(SMALL('Data
Sheet'!$A$1:$E$1,COLUMN()),'Data Sheet'!$A$1:$E$1,FALSE))

Copy to cells B1:E1, then down for as many rows as you have data on your
data sheet (named, for this example, "Data Sheet")

This assumes that your dates are in row 1 of sheet Data Sheet....

HTH,
Bernie
MS Excel MVP
Click to expand...

Thanks, Bernie. This is super - it works out really well.
 
Back
Top