Sorting Dates MonthWise and getting Unique Values

**e4excel** · 01-05-2011, 06:45 AM

Sorting Dates MonthWise and getting Unique

I had already got a solution on a similar query last week from the great DO..

http://www.excelforum.com/excel-work...ly-months.html

with the Query : Is Multilookup possible alongwith LIKE SORTING for only MONTHS..

However now though I have got the monthwise sort done is it possible to also get only the Unique Values..
In the otherquery I was pulling data for all the Dates but now I need to just get One MOnth -> for all the Policies.

Already have a few helper columns which I was already using to get the Unique values but dont know how to sort them or Sort them and also get Unique values...

In another sheet i need to get a Dropdown of only those MOnths which are relevant for a Particular person..

Please find the same file but just refer the Sheet "Rishi K".

Regards
E

**DonkeyOte** · 01-05-2011, 08:57 AM

Assuming you intend to maintain your existing helper cells then:

Please Login or Register  to view this content.

**DonkeyOte** · 01-05-2011, 02:42 PM

I inadvertently deleted your reply to the above post in which you stated

Originally Posted by madeup

"this works - great - but could we do it without using C & D ?"

(I deleted it whilst I was moving your other post from this thread to the transpose thread!)

Anyway, yes, you could but it would be quite ugly - or at least given the way my brain is presently functioning it would be.

First, you should really use at least one helper...to minimise array execution:

Please Login or Register  to view this content.

the above gives you the unique month count of those dates deemed valid based on YLY criteria (you could also use a Frequency Array)

Please Login or Register  to view this content.

**e4excel** · 01-06-2011, 01:42 AM

Thanks once again DO, thats ok for deleting the thread..

Even I erroneously it seems had posted a reply to some other thread..

Code:
V2:
=IF(ROWS(V$2:V2)>$X$1,"",DATE(YEAR(TODAY()),MIN(IF(($J$2:$J$15="YLY")*ISNUMBER($F$2:$F$15),IF(ISNA(MATCH(MONTH($F$2:$F$15),MONTH($V$1:$V1),0)),MONTH($F$2:$F$15)))),1))
confirmed wtih CTRL + SHIFT + ENTER (note Enter alone will not suffice)
copied down to V15

I tried this it does not work it gives an invalid month with the order as
1-Dec-2010
1-Jan-2011
1-Feb-2011
1-Jun-2011

Instead of the correct order from your ISt solution..
1-Jan-2011
1-Feb-2011
1-Jun-2011
1-Jul-2011

Regards
E

**DonkeyOte** · 01-06-2011, 04:21 AM

I didn't test with text header... one possibility (there are plenty)

Please Login or Register  to view this content.

**e4excel** · 01-06-2011, 04:52 AM

Wow this is the best one If I combine the value in X1 in the last code then I get the direct results..

Simply amazing..

I have not been able to figure out the formuls till now..

The role of "@" in the X! and N(V1) TILL NOW..

**DonkeyOte** · 01-06-2011, 04:57 AM

Originally Posted by e4excel

Wow this is the best one If I combine the value in X1 in the last code then I get the direct results..

If you're saying you are embedding X1 into the V2:V15 formulae (so as to remove the helper) then that's a (very) bad thing to do if efficiency of calculations is even of remote interest to you.

Repeating calculations where result is constant is one of the first things you should look to avoid.
Where the calculation being repeated is expensive (as is the case here) this practice would be deemed (at best) naive.

edit: generally speaking (though not always by any means) where we advise using a helper cell (or cells) it's for good reason.

**e4excel** · 01-06-2011, 05:10 AM

Originally Posted by DonkeyOte

Repeating calculations where result is constant is one of the first things you should look to avoid.

edit: generally speaking (though not always by any means) where we advise using a helper cell (or cells) it's for good reason.

No no I just tried doing it but I was anyways not going for that..
But as per your suggestions, I am not going to incline on non-helper codes..as I totally agree that repeating calcualtions would not be a good idea..rendering the code slow..and complex to refer at a later stage..