Avoiding array if at all possible

[MrCrow]

Hey Excel friends,

I have a daily report i am trying to make that i made that had at one point over a thousand array formulas before i really thought about it... took a VERY long time to run. I got around every column having an array besides one by using vlookup and index/match. its still a bit slow so i wanted to ask about this last column.

Quick overview - i have a automatically generated excel table that looks at appointments and appointment times for trucking companies. I have a report based off the table to make my bosses life a bit easier. I was able to get all the data for most of this example box below with Vlookup based off the BOL #. Now i want to generate each unique BOL for each time the trucking name pops up in the list without using an array, because the total report is around 800 lines long...

Example box

I found two ways to get a unique list of each time a different number pops up. One is array which the BOL lines that this array works in is using this code :
{=IFERROR(INDEX(Data!A:A,SMALL(IF(Data!B:B=$A$95,ROW(Data!B:B)),ROW(Data!A1))),"")}

and the other is index match, which the code on the side that gets each unique company name uses without an array now:
=IFERROR(INDEX(Data!$B$3:$B$1000,MATCH(0,INDEX(COUNTIF(N$7:N7,Data!$B$3:$B$1000)+(Data!$B$3:$B$1000=""),0),0)),"0")

Now the issue i have is that i cant find a way to get a second match, that only looks at the lines with "Schubert trucking" for example. Am i stuck with the array or is there any idea you guys have that can get around the array, or even keep it but speed up the calculations? Is there a VBA option i can use? I have 2 small VBA steps in this report so adding in another wouldn't hurt much.

Thanks!

 

[jasonb75]

Approximately how many rows of data are in the Data sheet?

Your array formula is processing over 1 million rows, so if you only have 10000 rows with data then 99% of the effort is being wasted. If you have a variable number of rows then try a dynamic range (in sheet or named).

Create a named range called DataRange which refers to =Data!$B$3:INDEX(Data!$B:$B,MATCH(1e+100,Data!$B:$B))
Note that the formula above assumes that column B contains numbers, if it is text then change 1e+100 to "zzz" including the double quotes.

Then use this formula in place of your array formula

=IFERROR(INDEX(Data!$A:$A,AGGREGATE(15,6,ROW(DataRange)/(DataRange=$A$95),ROWS(B$3:B3))),"")

Where B$3:B3 refers to the cell that contains the first formula before you fill down. This formula doesn't need array confirming.

As an FYI, your second formula is still an array, it just uses alternative functions to circumvent the Ctrl Shift Enter confirmation ? The formula that I've suggested does the same, but should be more efficient. I don't think that I've answered your question fully, but hopefully it is a step in the right direction.

Screen captures are confusing when it comes to formula rewrites because not all of the necessary information is visible.

 

[MrCrow]

hey thanks!