VB code needed for sorting function - please help!

[monica S]

Hi,

This has been driving me crazy... hopefully someone out there can help.

If I have an ORIGINAL range of dates in cells A1:A5:

22-Apr-04
30-Apr-04
22-May-04
22-Aug-04
22-Sep-04

and a TARGET date of say 16-Jun-04 in cell B1.

I would like to have a FUNCTION that will 'read' the OriginalDates, take the TargetDate into account, and then just INCLUDE or INSERT the target date among this original range of dates. The function would return the new dates in ascending order (i.e. with dates closest to now coming first and dates furthest from now coming last). The function would have to be typed in as an ARRAY function (with the { } bracketing the formula and closing it using Ctrl+Shift+Enter) and would return 1 more row than what is passed through OriginalDates argument.

so, the function would have two arguments and go something like this:

{=IncludeDate(OriginalDates,TargetDate)}

or;

{=(A1:A5,B1)}

In our example, the function would return the following sorted set of dates (with the June 16, 2004 date included and SORTED among the original set of dates):

22-Apr-04
30-Apr-04
22-May-04
*16-Jun-04*
22-Aug-04
22-Sep-04

in cells C1:C6 as a NEW OUTPUT RANGE of dates.

In this case, we would copy our function down 6 rows (one more than the OriginalDates) and close it with the Ctrl+Shift+Enter to make it an ARRAY of 6 rows.

A MACRO WILL NOT HELP IN THIS CASE. ALSO, USING THE DATA --> SORTING FUNCTIONALITY WILL NOT HELP IN THIS CASE. TRUE, BOTH WILL WORK, BUT THIS PROJECT CALLS FOR A **FUNCTION** TO HANDLE THE TASK.

Any ideas on how to go about doing this?

Thank you very much!
~ Monica

 

[monica S]

One question: How do we "clean up" repeating dates in the list? For instance, if we have the following dates in D5:D9:

15-Apr-04
15-May-04
15-Jun-04
15-Jul-04
16-Apr-04

with 16-Apr-04 being the date "to include" that we added to the end of the list,

the ARRAY formula in Cells H5:H9:

{=SMALL($D$5:$D$9,{1;2;3;4;5})}

will give us this sorted list:

15-Apr-04
16-Apr-04
15-May-04
15-Jun-04
15-Jul-04

BUT, if we have 15-May-04 as the date that we are adding to the end of our list, then the formula will return this to us:

15-Apr-04
15-May-04
15-May-04
15-Jun-04
15-Jul-04

As we can see, 15-May-04 repeats. It's there twice. Is there a way to see only NON-REPEATING dates in the output list?

Thanks,
~ Monica

 

[Yogi Anand]

Hi Monica:

One of the ways would be to use AdvancedFilter to extract Unique Values. Post back if you need to discuss this further.

 

[monica S]

This won't really work in my application, because I have everything set up as formulas. Any idea how to make unique records with formulas?

Thanks,
Mon

 

[Yogi Anand]

Hi Monica:

Another way would be to use the UniqueValues function from the MoreFunc Add-in as illustrated in the following ...

Book1
	A	B	C	D
1	MonicaDates		UniqueDates
2	15-Apr-04		15-Jul-04
3	15-May-04		15-Jun-04
4	15-May-04		15-May-04
5	15-Jun-04		15-Apr-04
6	15-Jul-04
Sheet1

Would this do?

 

[Yogi Anand]

And if you don't have or can not install the MoreFuns Add-in, then here is another way ...

Book1
	A	B	C	D	E
1	MonicaDates		UniqueDates	4
2	15-Apr-04		04/15/04	1	2
3	15-May-04		05/15/04	1	3
4	15-May-04		06/15/04	2
5	15-Jun-04		07/15/04	1	5
6	15-Jul-04			1	6
Sheet1 (2)

I have used columns D and E for intermediary calculations.

Formula in cell D2 is ... =COUNTIF($A$2:$A2,$A2)
this is then copied down

Formula in cell E2 is ... =IF(D2=1,ROW(D2),"")
this is then copied down

Formula in cell D1 is ... =COUNTIF(D2:D6,1)

and finally formula in cell C2 is ... =IF(ROWS($1:1)<=$D$1,INDEX($A$1:$A$6,SMALL($E$2:$E$6,ROWS($1:1))),"")

and this is then copied down

I hope this helps!

 

[monica S]

This is great Yogi! Thank you so much! Is there a way that we can incorporate the formulas in columns C and D in your example into only ONE formula in column C that can handle the whole opertion (this way, we can short-cut the intermediary calculations) in columns C and D?

Thank you in advance!
~ Monica

 

[monica S]

Sorry Yogi!

My last post SHOULD READ:

This is great Yogi! Thank you so much! Is there a way that we can incorporate the formulas in columns D and E in your example into only ONE formula in column C that can handle the whole opertion (this way, we can short-cut the intermediary calculations) in columns D and E?

Thank you in advance!
~ Monica

 

[Yogi Anand]

Hi Monica:

Here is a solution with one intermediary column ...

Book1
	A	B	C	D
1	MonicaDates		UniqueDates	4
2	15-Apr-04		04/15/04	2
3	15-May-04		05/15/04	3
4	15-May-04		06/15/04
5	15-Jun-04		07/15/04	5
6	15-Jul-04			6
Sheet1 (4)

I still think your best bet is using the AdvancedFilter to extract unique records -- however, you know your project constraints and preferences and I don't.

Anyway, I hope this helps!

 

[monica S]

Thank you. This brings up another question: What happens if the original values in cells A2:A6 are all unique values. C6 shows a #NUM! error. Any idea how to fix this?

Thanks again,
~Mon

P.S. The AdvancedFilter is accessed through the Data --> Filter --> AdvancedFilter drop down menus? If this is what you're referring to, then it won't work. My solution has to come from the formulas that we're working on. Thanks Yogi!

 

[Zack Barresse]

i don't get that error at all monica. do you have the same formulas:...(using Yogi's formulas)..

Book4
	A	B	C	D
1	Dates	UniqueDates
2	15-Apr-04	15-Apr-04	2
3	14-May-04	14-May-04	3
4	15-May-04	15-May-04	4
5	15-Jun-04	15-Jun-04	5
6	15-Jul-04	15-Jul-04	6
Sheet4 (2)

the only drawback to this, is that it doesn't sort like the UNIQUEVALUES will. you can sort, but it would take more than Yogi's two columns i believe. check out aladin's post(s) here:

http://www.mrexcel.com/board2/viewtopic.php?t=73509

hth