Replacing an Arrey formula, but with what?

[Excelllllllllllllllllllll]

Hello!

I have 2 of those curly bracketed formulas that I'm using to basically VLOOKUP repeat values that have different data in the cells next to them, but are slowing my file down so much macros literally take 5 times as long to run.

Can you awesome guys help me replace them with something less intensive?

Cell B3 = 1
Cell B4 = $B3+1

Cell C3 is:

=VLOOKUP($B3,Quote_Request!$A$14:$C$52,3,2)

Cell D3 is:

=IFERROR(VLOOKUP([@[Item Code]],Quote_Request!$C$14:Quote_Request!$L$52,8,FALSE),"0")

Cell E3 is:

[COLOR=#ff0000]{=IFERROR(INDEX($Q$3:$Q$50000,SMALL(IF($C3=$N$3:$N$50000,ROW($N$3:$N$50000)- MIN(ROW($N$3:$N$50000))+1,"0"), ROW()-2)),"0")}[/COLOR]

Cell F3 is:

[COLOR=#ff0000]{=IFERROR(INDEX($R$3:$R$50000,SMALL(IF($C3=$N$3:$N$50000,ROW($N$3:$N$50000)- MIN(ROW($N$3:$N$50000))+1,"0"), ROW()-2)),"0")}[/COLOR]

And then everything is copied down to Row 1,000.

Please help, MrExcel users. You're my only hope.

 

[Fluff]

Do you really need to run that formula over over50,000 rows?
You should limit array formula as much as possible.

 

[Excelllllllllllllllllllll]

Do you really need to run that formula over over50,000 rows?
You should limit array formula as much as possible.

The data I analyze is between 10,000 and 30,0000 normally, so 50K is just a safety buffer.

The gist of it is that I need a formula that looksup all repeat values of a targeted cell. Is there a way to do this without arreys?

 

[James006]

Hello,

With so many rows ... you should drop Array formulas ... and use a macro ...:wink:

 

[Fluff]

You could try
=IFERROR(INDEX($Q$3:$Q$50000,AGGREGATE(15,6,(ROW($Q$3:$Q$50000)-ROW($Q$3)+1)/($N$3:$N$50000=$C3),ROWS($A$1:$A1))),0)

 

[jasonb75]

With such formulas, I prefer to set index to the whole column to avoid the extra calculations needed for adjusting the position of the first row, try this one in E3, drag right to F3 then fill down both columns.

=IFERROR(INDEX(Q:Q,AGGREGATE(16,6,ROW($N$3:$N$50000)/($N$3:$N$50000=$C3),ROWS(E$3:E3))),0)

If you return a blank instead of 0 for an error, then you could also eliminate the array calculation on subsequent rows once the first error has occurred by using a simple logical test.

=IF(E2="","",IFERROR(INDEX(Q:Q,AGGREGATE(16,6,ROW($N$3:$N$50000)/($N$3:$N$50000=$C3),ROWS(E$3:E3))),""))

However, using this with 0 would ignore all data below a genuine zero value in the results should one occur.

Limiting the array range to the actual data by using dynamic ranges would also help, this is even easier if your data is in structured tables. There is a structured table reference in one of your formulas, so at least part of your data is in this format, if not all of it.

Finally, you could run your macros with manual calculation if you're not already doing so (if you are then it could be something else slowing them down).

 

[Excelllllllllllllllllllll]

Hello again, jasonb75!

try this one in E3, drag right to F3 then fill down both columns.

Neither of the formulas are returning more than one result (even though the first result is not a 0).

However, using this with 0 would ignore all data below a genuine zero value in the results should one occur.

I never have 0 in the values. One column is looking up dates and the other one quantities of stuff that's been ordered so it'll always be 1 +

Limiting the array range to the actual data by using dynamic ranges would also help, this is even easier if your data is in structured tables.

I put a table where I paste the data so it's all structured now and I changed my formulas to refer to the entire table column, but now the simplest macro of "hide this column" either freezes excel or is processing for more than 5 minutes and I got bored waiting for it, and terminated the excel instance. Most of my macros are simple hide/show columns & rows on another sheet without any ifs or anythign else heavy on processing.

 

[Excelllllllllllllllllllll]

Bump :S
Any ideas?

 

[jasonb75]

With the table references, you would need the formula with the adjustment (Fluff's version, or a variation of it), wonder if anyone notices the difference

=IF(E2="","",IFERROR(INDEX(Q$3:Q$50000,AGGREGATE(16,6,ROW($N$3:$N$50000)/($N$3:$N$50000=$C3),ROWS(E$3:E3))-ROW($N$3)+1),""))

Or using table references as an example, [Results] refers to the table column equivalent to Q3:Q50, [Criteria] to N3:N50.

=IF(E2=0,0,IFERROR(INDEX(Table1[Results],AGGREGATE(15,6,ROW(Table1[Results])/(Table1[[Criteria]:[Criteria]]=$C3),ROWS(E$3:E3))-ROW(Table1[[#Headers],[Results]])),0))

Hope this helps