VB code needed for sorting function - please help!

monica S · Apr 23, 2004

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

Greg Truby · Apr 29, 2004

Monica,

You'll get an N/A return once the number of cells with the array-formula in it exceeds the number of elements being returned. While you input six cells, you only get five outputs due to the duplicate entry.
<hr>
Nate,
I ain't a sayin' this is the most efficient way-a doin' things. Yer knowledge in that area well exceeds mine. All's I'm a sayin' is that most efficient ain't always preferred. If'n 'twas we'd not see nary an SUV on the road and every hotel would be a-la Motel 6. Now seein's ta how you've got a bee in yer bonnet about this, how's 'bout doffing that bonnet; donning yer "professor cap" and explaining just how big of a hit this approach costs us.

Does "...marshaling a variant variable containing an array of x elements per call..." grow expontially? If ten inputs and ten outputs, are we burning 100 addresses? And if we step to 20 inputs and 20 outputs we consume 400 addresses?

monica S · Apr 29, 2004

Greg,

Do you know why the formula:

{=IF(ISNA(Fubar2(B3:B6,C3)),"",Fubar2($B$3:$B$6,$C$3))}

copied down from D2:D7

does not work in cleaning up that #N/A error value that I referred to in cell D7?

Shouldn't the "ISNA" part of the formula see that there is an #N/A in cell D7 and just leave that cell blank? Or, am I missing something?

Thank you!
~ Monica

Zack Barresse · Apr 29, 2004

hi monica,

i'm not tryin to get between Greb and Nate, they're waay outta my league here. but have you tried replacing your ISNA with ISERROR to blanket all errors? i've done that with success (mirroring an equation like yours, not the fubar though).

Greg Truby · Apr 29, 2004

Monica,

I can't cook up a way formulaically to suppress the #N/A. What we can do it alter the function a bit. You could edit the tail-end of the function to look as follows:
'...
 strFormat = oldDates.Cells(1, 1).NumberFormat
 ReDim newArr(0 To (oldDates.Count + newDates.Count - 1))
 For i = 0 To myCol.Count - 1
 newArr(i) = Format(myCol(i + 1), strFormat)
 Next i
 For i = myCol.Count To UBound(newArr)
 newArr(i) = ""
 Next i

 Set myCol = Nothing
 Fubar2 = Application.Transpose(newArr)
End Function


This will now return empty strings when all unique outputs are exhausted. Note however that this will still produce an #N/A return error if the number of cells in your output range exceeds the number of cells put into the formula. Examples:
C1:C10 = {Fubar2(A1:A5,B1:B5)} and there are two duplicates - C9:C10 would contain null strings.
But if C1:C12 = {Fubar2(A1:A5,B1:B5)} then C9:C10 still nullstrings, but C11:C12 will return #N/A.

monica S · Apr 29, 2004

I would like to send a most sincere "THANK YOU" to Greg, Nate, Yogi, Firefytr and Aladin. I really appreciate the time that you spent contributing to this question. I tried all of your suggestions, but in the end decided that the user-defined function most suited my needs. I certainly learned a lot from all of your posts and I am very impressed by the depth of your knowledge. I will be sure to save all of your contributions for future reference.

Thank you again,
~ Monica

NateO · Apr 29, 2004

Greg Truby said:
Nate,
I ain't a sayin' this is the most efficient way-a doin' things.

Great then we agree. I'm going to take it one step further though, and say that you, Aladin and I should be taken out back to the woodshed and be flogged for our UDF propositions. Sorry gentlemen, they're not just inefficient, they're bordering on workbook corruption.

Any innocent person reading this thread should be informed that while all three of these propositions make for interesting mental masturbation, they should not be implemented in a workbook where reasonable performance is expected.

If'n 'twas we'd not see nary an SUV on the road and every hotel would be a-la Motel 6.

There is almost no such thing as an efficient automobile based on what we know to be possible.

http://www.bmwusa.com/Joy/Drive/Technology/Hydrogen.htm

I drive a Grand Cherokee, and come January in Minnesota, it's a lot more efficient than an S-Type Jag. :wink:

Now seein's ta how you've got a bee in yer bonnet about this, how's 'bout doffing that bonnet; donning yer "professor cap" and explaining just how big of a hit this approach costs us.

First things first. Let's remove a major drag:

ReDim newArr(0 To myCol.Count - 1, 1 To 1)
For i = 0 To myCol.Count - 1
newArr(i, 1) = Format(myCol(i + 1), strFormat)
Next i

Set myCol = Nothing
Fubar2 = newArr
End Function

Still, you're taking something really bad and making it better, yet still, it's really bad. You don't even need to time it to come to this conclusion. Sort the range and use a filter.

NateO · Apr 29, 2004

monica S said:
I would like to send a most sincere "THANK YOU" to Greg, Nate, Yogi, Firefytr and Aladin.

You're welcome.

I tried all of your suggestions, but in the end decided that the user-defined function most suited my needs.

For the sake of innocent readers, this isn't accurate, but so be it.

Have a nice day.

Greg Truby · Apr 29, 2004

Nate, I forgot yer up North where SUV's may indeed be pragmatic.

As an illustration to any innocent bystanders that may read this thread and be lured into trying this I did test the performance on this rascal and at least on my machine things were fine under about 500 cells in the combined ranges. After that things go downhill in a hurry. Get over 1,000 cells and the re-calc times would really become onerous. Scatter graph the following and Nate's point become very clear - this approach gets expensive quickly.

dateaddfunction.xls

C

D

E

F

1

Run

CellCount

Time

2

1

100