Alternative to Array formula

[Jnrrpg11]

Hello Everyone

Im wondering if anyone knows of a faster alternative to this Array formula-

{=INDEX('(System) SC Country 1'!$C$5:$C$5000,MATCH(0,IF(('(System) SC Country 1'!$D$5:$D$5000="Incomplete")*('(System) SC Country 1'!$G$5:$G$5000="Routine")*('(System) SC Country 1'!$J$5:$J$5000<>"JANITORIAL SERVICES-DAILY"),COUNTIF($H$5:$H5,'(System) SC Country 1'!$C$5:$C$5000)),0))}

So in this formula we are listing values that are in '(System) SC Country 1'!$C$5:$C$5000'.

but we are removing values based on the 3 criteria below -

(System) SC Country 1'!$D$5:$D$5000="Incomplete"
(System) SC Country 1'!$G$5:$G$5000="Routine"
(System) SC Country 1'!$J$5:$J$5000<>"JANITORIAL SERVICES-DAILY"

And the list runs from H5 to H1004 which is highlighted in red.

I should say that this formula works but the problem is that its horrifically slow.
It takes approx. 8 times longer to run that the other 22 sheets in the workbook combined.
I have used this formula before from small Unique Distinct Lists but I actually don't need the output to be unique/distinct as the values in C5:C5000 are already non repeating.

Has anyone used an alternative way of creating a list like this that would run faster than the Array formula that I have here.


Thanks in Advance and Kind Regards

 

[Fazza]

Difficult for me to be more specific yet. Suggest you post sample data.
Likely faster approaches are, if it suits the task, advanced filter. Also VBA or a query. Maybe a pivot table.

 

[offthelip]

If you are looking for speed, using variant arrays with VBA is the way to go. The fact that all the data in your "if" array formula is on one sheet means that you can load all of that into one variant array and search it really fast. Have a look at this thread to get and explanation of how to do it:https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[Fazza]

found some old threads where array formulas were taking over an hour, even 6 hours. using queries got the job done in like a second (with no VBA required, btw)

https://www.mrexcel.com/forum/excel-questions/318970-pivot-table-views.html
https://www.mrexcel.com/forum/excel-questions/403690-whats-better-than-sum-if-array-formulas.html
https://www.mrexcel.com/forum/excel-questions/581022-geo-fencing-formula.html

 

[Jnrrpg11]

Hey Folks,

Thanks for your reply. Theres some super interesting stuff in these links.
Unfortunately, I'm not great when it comes to VBA. Im learning but I'm finding the articles hard to follow.
If you guys have the time id love to ask some follow up questions.
So is it likely that using a VBA option to write out a list of values would be quicker than the a INDEX/MATCH/COUNTIF array formula?
Also if i had a basic table like the one below and i want the blank column to list the receipt numbers with insurance (eg.1001,1003,1004,1005...),
How difficult would the code for that be?



Thanks again for taking time to look at this.

Kind Regards

 

[Fazza]

First step, insert some rows above the starting tables.

Above the first "Insurance Taken" (position not critical actually) repeat "Insurance Taken". Say in cell "B1" and under it in cell B2 enter "Insurance"


Change the second "Insurance Taken" (where you want the Receipt No results) to "Receipt No"

Now use advanced filter. ALT-D-F-A
near the top choose "Copy to another location"
for list range, enter the range for the main data. Maybe something like A4:Bxx
for criteria range, the newly inserted mini table, say B1:B2
for copy to, select the cell that said "Insurance Taken" and was changed to "Receipt No". Say D4
Now press "OK" & see the result

PS If you google for "Excel advanced filter tutorial" there will be lots of examples/help