Array Alternative

Hi,

I'm looking for an alternative to the current array formula I'm using.

If I have the following table:

A B C
1 Order No. Vendor Hours Left
2 10043572MI Company 1 13.00
3 10043574MI Company 1 13.00
4
5 10043585NJ Company 2 23.00
6 10043625IL Company 2 37.00
7
8 10043590FL Company 3 23.00
9 10043591FL Company 3 7.00
10 10043595MI Company 3 28.00
11 10043603NJ Company 3 28.00
12 10043619OR Company 3 37.00

And I want to return an list separting the orders on a seperate sheet.

I currently use:

Please Login or Register  to view this content.

As an array. Ctrl, Shift, Enter.

So it looks for the company name on "Duplicates!$A$3" and matches it in VendorEmail!B:B and returns the corresponding value in VendorEmail!A:A. VALUE(P2) is the # rows down. So P2=1 for company 3, we get:

8 10043590FL Company 3 23.00

P2=4 we get:

11 10043603NJ Company 3 28.00

Is there a faster alternative to this array formula?

Thank you!

The greatest contributing factor to the slowness of that formula is the use of entire column references like A:A
You should restrict all those ranges to the actual used area of the sheet like A$1:A$1000

The indirect probably doesn't help much either.
But I'm not clear on what the indirect is there for, what exactly is in Duplicates!$A$3

Wow, just typed this twice in a row and hit 'reply to thread' instead of post quick reply.

Anyways, I will definitely get to those after I solve this array issue.

Duplicates!$A$3 is the company name I'm looking up/matching. Duplicates!$A$4 will be another and so on.

Thank you for the feedback!

Then I don't see the need for it to be INDIRECT..
VendorEmail!B:B=INDIRECT("Duplicates!$A$3")
can just be
VendorEmail!B$1:B$1000=Duplicates!$A$3

VBA on Duplicate! removes duplicate company names and therefore deletes cells creating a reference error.

Maybe add code to your existing code that puts the formula back in the cells correctly ?
Just going through things that cause slowness, indirect is a big one.

Maybe instead of indirect, try Index
VendorEmail!B$1:B$1000=INDEX(Duplicates!$A:$A,3)

That certainly works so far. Thank you!

Do you know of any alternatives to the index small array?

Anybody have any suggestions on alternatives to the array?

Please Login or Register  to view this content.

I said ALL the ranges.
And each should be the same rows (the one in the index that used to be indirect is ok)

Originally Posted by theskyscraper1

Please Login or Register  to view this content.