Gurus2, The Return of the Jet Eye (Arrays)

nbrcrunch · Jan 26, 2007

Do you have an "eye" for spotting fast, jet-speed array solutions? Are you a "Jet Eye Master"?

Post your most challenging/interesting problems and how array formulas saved the day. (Try to generalize the issue and resolution so that others can see the value of using your solution.)

Arrays have long baffled me (as they are used in Excel formulas). Now, I can formulate some simple ones, but I still have to whip out my saved notes--downloads from MrExcel posts. But it still doesn't come "naturally" to me. I love SUMPRODUCT but sometimes it is just better to create an array formula.

Sooo, you Jet Eye Masters out there, lets hear from you.

:wink:

hatman · Jan 29, 2007

Had a situation where I was handed a set of data that had been entered by a bunch of monkeys. The detriment in my case was concatenation of data in a single field that should have been entered in multiple fields. Worse was that the number of fields in the single field varied by dataset. And the last insult was that the monkeys were actively adding to the data, and doing stuff with that data as entered such that I wasn't allowed to do a simple text-to-columns. Further, the data owners were skeptical of VBA for a variety of reasons.

This was my solution, which I eventually expanded to help someone on this board with an Alphabetizing problem. This copy is not from the original data, but rather I built it after the fact as a template for future reference, so it's slightly artificial...

[HTML removed by admin]

HTML size reduced by Hatman... sorry about the problem on the first go-around... system wouldn't allow me to fix it after posting...

Alphabetize.xls

A

B

C

D

E

1

IfixedthisversiontohandleCapitalLetters,andmultiplespaces:

2

IfixedthisversiontohandleCapitalLetters,andmultiplespaces:

3

4

NaturalOrder

AsciiCode

Rank

AdjustRank

Output

5

6

7

8

9

10

11

12

13

14

15

Sheet1

Code:

=IF(ISERROR(FIND(" ",$A$2,SUM(LEN($A$5:A5))+1)-SUM(LEN($A$5:A5))),RIGHT($A$2,LEN($A$2)-SUM(LEN($A$5:A5))),MID($A$2,SUM(LEN($A$5:A5))+1,FIND(" ",$A$2,SUM(LEN($A$5:A5))+1)-SUM(LEN($A$5:A5))))

Fazza · Jan 31, 2007

When I think of fast array solutions I think of arrays in VBA - they offer "jet" fast ways of handling lots of data. I almost never use array formulas in worksheets. They are usually horribly slow when there is a large amount of data. There was an instance a few years ago though that I had a neat solution with array formulas.

Situation was ranking of values within different groups. Such as

Code:

type   value   rank
alpha      7
&          1
alpha      8
&          8
&          1
-          3

So the requirement was to give in the first rank cell the rank of value 7 amongst all of the alpha values. And then the rank of the next value 1 amongst all of the "&" values. The type identifiers could be anything. Some people had been working for a couple of days to work out some code and were still trying to work it out. An array formula approach gave the answer readily.

First, selecting the first answer cell, create a defined name "MyArray" with formula

Code:

=IF('type' cell in that row=range(all 'type' cells),range(all 'value' cells))

Then array formula in the cell is

Code:

=COUNTIF(range(all 'type' cells),'type' cell in that row)-SUM((adjacent 'value' cell>=MyArray)*1)+1

Is is pretty amazing and most innovative to use the array formula in the named range to return an array of booleans and values.

The answers are that the first (alpha) 7 is rank 2
then & 1 is rank 2
and alpha 8 rank 1
and & 8 rank 1
and & 1 rank 2
and - 3 rank 1

Cheers, Fazza

hady · Feb 2, 2007

Code:
Had a situation where I was handed a set of data that had been entered by a bunch of monkeys. The detriment in my case was concatenation of data in a single field that should have been entered in multiple fields. Worse was that the number of fields in the single field varied by dataset. And the last insult was that the monkeys were actively adding to the data, and doing stuff with that data as entered such that I wasn't allowed to do a simple text-to-columns. Further, the data owners were skeptical of VBA for a variety of reasons.

This was my solution, which I eventually expanded to help someone on this board with an Alphabetizing problem. This copy is not from the original data, but rather I built it after the fact as a template for future reference, so it's slightly artificial...

[HTML removed by admin]

HTML size reduced by Hatman... sorry about the problem on the first go-around... system wouldn't allow me to fix it after posting...

Code:

=IF(ISERROR(FIND(" ",$A$2,SUM(LEN($A$5:A5))+1)-SUM(LEN($A$5:A5))),RIGHT($A$2,LEN($A$2)-SUM(LEN($A$5:A5))),MID($A$2,SUM(LEN($A$5:A5))+1,FIND(" ",$A$2,SUM(LEN($A$5:A5))+1)-SUM(LEN($A$5:A5))))

Hatman, you are sorting alphabet in complicated way and too many helper cells, Try this solution.
I assume your data to be sorted alpabhet is in A1:A10
and the output of sorted data is in B1:B10.
here is the trick :
select B1:B10, and put in below formula

Code:

=INDEX(IF(A1:A10=""," ",A1:A10),MATCH(SMALL(COUNTIF(A1:A10,"<="&A1:A10)+ROW(A1:A10)/10^10,ROW(INDIRECT("$1:$"&ROWS(A1:A10)))),COUNTIF(A1:A10,"<="&A1:A10)+ROW(A1:A10)/10^10,0))

use CSE to execute,
so you get data in B1:B10 in sorted alphabet condition.

regards,
Hady

NateO · Feb 3, 2007

Hello,

When I think of fast array solutions I think of arrays in VBA - they offer "jet" fast ways of handling lots of data. I almost never use array formulas in worksheets.

I use both, arrays are the most underrated aspect of Excel, in my opinion, in both the front and back ends... Extremely powerful...

But what if you combine the two, that is VBA and Array-entered functions?

Here's the question, from someone using Access (this is key to the response):

How easy would it be to add in some code that will color every other set of 10 lines Yellow (excluding row 1)....So for example, rows 2-11 would be yellow, 12-21 would be white, 22-31 would be yellow and etc.

I would want to repeat this until line 301...
{snip}

No problem. But let's get creative!

With a little VBA from Access, a solid working knowledge of Excel's Object model, and a little matrix-math/Excel CSE worksheet function trickery, we can solve the problem with a one-liner, basically:

Code:

Sub foobar()
Dim xlApp As Object, xlWb As Object, xlWs As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add(1)
Set xlWs = xlWb.Worksheets(1)

xlWs.Range(Join(xlApp.Evaluate("""A""&Transpose((Row(1:15)-1)*20+2)&" & _
    """:A""&Transpose((Row(1:15)-1)*20+11)"), ",")). _
    EntireRow.Interior.Color = vbYellow

xlApp.Visible = True

Set xlWs = Nothing:             Set xlWb = Nothing
Set xlApp = Nothing
End Sub

Perhaps not the lightest weight calculation, especially when applied Out-of-Process...

But, perhaps an interesting alternative to looping through Ranges or combobulating a non-contiguous Range via Union(), also Out-of-process, and both being quite slow.

:wink:

hatman · Feb 3, 2007

Whoa Nate...

...I'd have to go brush up on all three of the methods you are using there before I could begin to understand that enough to appreciate the elegance. The only measure of elegance I have at first glance is the fact that you did in one line what I certainly would have attacked with a loop... I don't think I would have even gone down the Union() route, though I think I can at least see that one.

ray:

Gurus2, The Return of the Jet Eye (Arrays)

nbrcrunch

Well-known Member

Excel Facts

hatman

Well-known Member

Fazza

MrExcel MVP

hady

New Member

NateO

Legend

hatman

Well-known Member

Forum statistics

Share this page

Gurus2, The Return of the Jet Eye (Arrays)

nbrcrunch

Well-known Member

Excel Facts

hatman

Well-known Member

Fazza

MrExcel MVP

hady

New Member

NateO

Legend

hatman

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock