script to sort dates in excel

http://technet.microsoft.com/en-us/library/ee692895.aspx

no attachment ....

apologies, I thought it was atatched.
Book1.xlsx

This will give the lowest start date

=MIN(B2:B14)

and this will give the lowest stop date

=MAX(C2:C14)

Hi ssaqibh,
Min and max should be based on Serial column.

Not for points

Usng array formulas on whole columns is a VERY bad idea. Just because it can be done does not meant it should be. Always ringfence the ranges when using array formulas. Dynamic ranges spring to mind.

To return the serial number, you can use this non-array formula


smallest start date      =INDEX(A:A,MATCH(MIN(B:B),B:B,0))
largest stop date      =INDEX(A:A,MATCH(MAX(C:C),C:C,0))

If the smallest/largest date occurs more than once, only the first match will be returned.

cheers, teylyn

Hi teylyn,

using Index  only get serial number not the dates

You said above:

>> Min and max should be based on Serial column.

Now I don't understand what you want to achieve. Your workbook has three columns:

Serial   |      Warranty Start Date       |  Warranty Stop Date

What exactly do you want to do? If you want to return the dates instead of the serial numbers, then use

smallest start date      =INDEX(B:B,MATCH(MIN(B:B),B:B,0))
largest stop date      =INDEX(C:C,MATCH(MAX(C:C),C:C,0))

I still don't understand how "based on Serial column" comes in. Please clarify.

cheers, teylyn

sure, will try and update soon

macentrap, you did not respond to my second comment. Does it not do what you want?

Maybe macentrap is still waiting for the array formulas with whole column references to finish calculating .... The Index version is much faster.

Yes but I need to be sure that the results are what he is looking for instead of proposing the solution and then ending up with "this is not what I want"

Let's give it a few days. This is still the weekend in many parts of the world, and other work pressures may take precedence.

In the meantime, you may want to compare the performance of the array formula with the performance of the Index/Match combo (non-array) formula on a dataset of 500 k rows.

cheers, teylyn

Hi teylyn/ ssaqibh,

Apologies for late reply, got tied up with other work.

Yes ssaqibh, second one worked just fine.

teylyn, there are duplicate serial.
Like for warranty,

Serial ABC is supported from 1 Jan 2010 to 1 July 2010 on phone based support but for parts replacememt the warranty does not end till 31 Dec 2014.

Secnario is warranty will start from 1 Jan2010 which will be the minimum start date but will end on maximum stop date for every serial item.

so the formula will be it checks the serial and check for min start date and max stop date.

Please, Can this be achieved through Index?
do agree array is slow for 80000 lines

Thank You

thank you