array to range

[Corticus]

Wassup fellow Excelers,

I would like to take a single dimension array and display the values down a range of cells. Can I do this?

In this example, I create a 6 element array from the range("A1:A6").

Then I do some array manipulation (like quicksort).

Now I want to return the array to a range of cells.

Kind of like this, but without the error:

Book2
	A	B	C	D
1	F	#NAME?
2	E	#NAME?
3	D	#NAME?
4	C	#NAME?
5	B	#NAME?
6	A	#NAME?
Sheet1

In this case, I would want to use the formula just like I did, but [B1] would display the array(1), [B2] would display array(2) etc.

Thanks in advance for any help!

 

[NateO]

And you believe this is how to best proceed Juan?

 

[Juan Pablo González]

Mhmm, yes, well, the formula could be improved, but, my point is, return the entire array, and if you need an specific item, use INDEX instead of modifying the code...

 

[NateO]

That's where we diverge. I vote to modify the code (for reasons already mentioned).

I am agreeable to disagreeing, no hard feelings eh.

 

[Corticus]

You guys are awesome,

I was having the same internal debate that you guys are having, array formula or not...

I like both the solutions, they both worked great.

I really wanted to know how to return an array using an array formula , and by just using a regular formula. You guys got both!

Check it out:

sort_array.xls
	A	B	C	D
1	List	Juan's	NateO's
2	Z	C	C
3	D	D	D
4	F	F	F
5	G	F	F
6	F	G	G
7	R	H	H
8	W	R	R
9	H	W	W
10	C	Z	Z
Sheet1

At the risk of pissing someone off, I won't tell you which I like better!

Thanks guys, perfect solutions!

edit:

You need to assign something to the formula to get as the result!

Yeah, I know, but I left that out since it wasn't returning what I wanted.

-Corticus

 

[NateO]

Hello again Corticus, Quick tack-on. My udf isn't returning an array, simply an element of the array. Also, the sort is ascending by default, you can omit the last [optional] argument for the function if you want this, i.e.,

=strsort($A$2:$A$7,ROW()-1)

Have a good one.

 

[Juan Pablo González]

That's where we diverge. I vote to modify the code (for reasons already mentioned).

I am agreeable to disagreeing, no hard feelings eh.

No heart feelings... but I still don't understand your reasons.... I mean, your code already produces the entire array, right ? and then you modify the code to get one of the items, right ? I agree with the ascending/descending parameter... but, I *feel* that restricting the code to return only one element is not, mhm, what's the word?, efficient maybe?, in the long term...

But ok... different points of view after all !

 

[NateO]

I mean, your code already produces the entire array, right ? and then you modify the code to get one of the items, right ? I agree with the ascending/descending parameter... but, I *feel* that restricting the code to return only one element is not, mhm, what's the word?, efficient maybe?, in the long term...

See, I don't know, and time testing would prove it, but I'm too lazy.

You have to assign the function [a] value either way, so I'm not really modifying it (I am, but not *******izing it based on the goal set), I simply assign an element versus the entire array, just a different assignment, and seemingly, a smaller one. You assign arr1, I assign b(1) or b(2), etc...

And it seems to me a single element would consume less memory than the entire array (at a high level). If you want to populate cells with arrays then yeah, don't grab an element, but if you only want to see a single element, which is what the goal set appears to be, why return more?

Edit: I should add the strSort function has two limitations, the use of join means, as written, it requres vb => 6. And more importantly, it only functions on cell arrays of single characters. Why? Because when you stack an array into a string and then convert to bytes, you have a bunch of single characters. When elemtns are => 2 in length the array is bigger than what you want and housing the incorrect string returns. I see no point in correcting for with a byte array (which wants single chars, so use a variant array:<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> StrSort(myStr<SPAN style="color:darkblue">As</SPAN> Range, y<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>,<SPAN style="color:darkblue">Optional</SPAN> Descend<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Boolean</SPAN>)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN><SPAN style="color:darkblue">Dim</SPAN> b<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN>, i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>, j<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>, LwVl<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN>
b = Application.Transpose(myStr)<SPAN style="color:darkblue">For</SPAN> i =<SPAN style="color:darkblue">LBound</SPAN>(b)<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(b)
    LwVl = b(i)
    <SPAN style="color:darkblue">For</SPAN> j = i + 1<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(b)
        <SPAN style="color:darkblue">If</SPAN> Choose(-CInt(Descend) + 1, _
            b(j)< LwVl, b(j) > LwVl)<SPAN style="color:darkblue">Then</SPAN>
              LwVl = b(j)
              b(j) = b(i)
              b(i) = LwVl
        <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Next</SPAN><SPAN style="color:darkblue">Next</SPAN>
StrSort = b<SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>

Giving you:

Book2
	A	B	C	D	E	F
1	Input	Descending	Ascending	Input	Descending	Ascending
2	E	E	AA	5	5,000	2
3	B	D	AB	7	15	5
4	AB	C	B	10	10	7
5	C	B	C	5,000	7	10
6	D	AB	D	15	5	15
7	AA	AA	E	2	2	5,000
Sheet2

 

[Corticus]

Great info guys,

Just to follow up, all three of you provided me with information I needed. Aladin and Nate helped me to pull an element out of the array. The row() formula was what I was missing, I didn't know how to let the formula know what element to pull, without specifying it as an argument, which still needed to be done, but I didn;t think about using the row() function as an argument to eliminate the need for an extra "index" column that just counted 1, 2, 3...whatever as the element argument for the function.

Juan, your answer was more what I was originally expecting I would have to do, I suspected an array could be passsed to a range by array entering it {}, but the tranposition from 2d to 1d and then back to 2d was throwing me.

Nate, how does your sort fare against quicksort and other sort algorithems timed here:
http://www.microsoft.com/officedev/articles/movs102.htm

Microsoft did this little table, and quicksort seems to perform very well over the other sort algorithems, besides "counting sort" which seems to do the best overall, but I had some issues getting to work correctly.

Really, sorting wasn't the point of this thread, I was more interested in returning either the elements of an array to a range via formula, or return the array itself via an array-entered formula.

One last thing, I would like to make the array 2d again, so I don't have to use transpose() if I use the array entered formula, is this a reasonable way to deal with that in code:

Function sorter(rng As Range) As Variant

    Dim arr1() As Variant

    If rng.Columns.Count > 1 Then Exit Function

    arr1 = Application.Transpose(rng)

    QuickSort arr1
    
    sorter = Application.Transpose(arr1)
    
End Function

This lets me enter the array formula like:
{=sorter(range)}
which looks nicest to me.

Thanks for all your help, this is good stuff!

 

[NateO]

Looks reasonable to me, if you want a 2nd opinion.

My algorithm blows Quick Sort out of the water! Just kidding, who knows, use a timer:

http://www.mrexcel.com/board2/viewtopic.php?t=38740&start=6

I think Quick Sort is more of general purpose sort function to handle multi-dimmed arrays, etc... The sort method I posted is more tailored for a 1-d, but I didn't optimize by exiting the loop early or anything... Note too, I have extra function calls to give you the ascending/descending options, might want to pull this bonus functionality out for a more apples to apples look.

Bon chance.

 

[Corticus]

Oh yeah,

I definately liked the asc/desc argument.

Performance is kind of a mut point though, I just wanted to get the array on a range, and it looks like we got it!

Thanks for all your help Nate, even on my odd useless questions...

-Corticus