Speed up array / transform into formula

[mewashoo]

Hi All,

I am working on a table in Sheet2, that is going to import "reference numbers" from column K in Sheet1. I am using several different variables to determine which references to index, like must contain string "Reactive" in column O. Currently it's working

It looks like:

OPEN ACTIONS
REACTIVE
Reference	Contract	Address	Action
20379948
20520719
20520799
20545093
20686151
20689069
20692329
20703287
20703313
20729359

<tbody>
</tbody>

Columns "Contract", "Address" and "Actions" are easy as it's just index match comparing to column "reference", but "reference" has to spit out all matching record from different sheet.

Below array does work very well, however it's very slow and it takes up to 5 minutes for all calculations to finish.

Does anyone know any method how to achieve the same without array? Or make the array faster.

[LEFT][COLOR=#333333][FONT=Calibri][SIZE=2][COLOR=#000000][TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]{=IFERROR(INDEX(Sheet1!B:L,SMALL(IF(Sheet1!O:O="Reactive",IF((Area=Sheet1!B:B),IF((Sheet1!BE:BE="Open Actions"),ROW(Sheet1!B:B)-MIN(ROW(Sheet1!B:B))+1,"")),""),ROW(G2)),11),"")}



[/COLOR][/SIZE][/FONT][/TD]
 [/TR]
</tbody>[/TABLE]
[/COLOR][/SIZE][/FONT][/COLOR][/LEFT]

Sheet1 contains roughly 20k rows and 30 columns.
Any help highly appreciated. G2 in above array is incrementing by 1 each row(not referencing to any specific value), everything else is static.

 

[oldbrewer]

OPEN ACTIONS

required keyword

blue

REACTIVE

Reference

Contract

Address

Action

keyword

problem statement

20379948

red

20520719

blue

export all references where keyword = "blue"

20520799

green

20545093

red

20686151

blue

20689069

green

20692329

red

this macro was used to produce the lower table

20703287

blue

20703313

green

Dim refer(100), keyword(1000)

20729359

red

mymatch = Cells(1, 5)

For j = 4 To 13

If Cells(j, 5) = mymatch Then Sum = Sum + 1: refer(Sum) = Cells(j, 1): keyword(Sum) = Cells(j, 5)

Next j

For k = 20 To 20 + Sum

tot = tot + 1

Cells(k, 1) = refer(tot): Cells(k, 2) = keyword(tot)

20520719

blue

Next k

20686151

blue

End Sub

20703287

blue

<colgroup><col><col span="17"></colgroup><tbody>
</tbody>

 

[mewashoo]

Hi oldbrewer,

many thanks for replying.

I don't know much about VBA, always recording macros and editing. ​

I am not sure how to re-do above macro for my needs, but I bet that once I get it to running it will improve performance by a lot.

 

[oldbrewer]

first line creates two arrays to store information
second line cells(1,5) = row 1 col 5 = E1 = where required match is entered
third line is start of a repeating loop
fourth line checks if column E is the same as "mymatch" and if so put columns A and E into the arrays
fifth line is end of loop
sixth line start a new loop
seventh line advance a counter by 1
eighth line print out a line of results
end loop
end sub

just change the cell references to where your data is

 

[kvsrinivasamurthy]

If data starts from Row 2 in Sheet1
Try this ARRAY formula

=IFERROR(INDEX(Sheet1!$B$2:$L$25000,SMALL(IF((Sheet1!$O$2:$O$25000="Reactive")*(Area=Sheet1!$B$2:$B$25000)*(Sheet1!$BE$2:$BE$25000="Open Actions"),ROW(Sheet1!$B$2:$B$25000),""),ROW(G2))-ROW($B$2)+1,11),"")

 

[Aladin Akyurek]

@mewashoo

Looks like

OPEN ACTIONS
REACTIVE

are conditions. How about that Area bit in the formula, which is tested for being equal to Sheet1!B:B?