Excel xlsx using matrix take forever to save

I got a 40,000 excel records not more than 10 columns,  few columns using matrix formulae like
 
{=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))}

it took forever to save,   each Calculating (4 processor(s))   %,  took 3 mins to save,   mean it may take 3 x 100 = 300 mins to save,  OMG !

I am using excel 2016
Christopher YapAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
For the formula you showed, you can add helper columns and then use simple Index/Match combination to get the desired output.
Otherwise, for one cell, the formula is checking 40000 cells for the condition Isnumber and then returning the first True returned. And if you copy this formula down for just one column, the formula in that columns will calculate for 40000*40000 cells.
0
Christopher YapAuthor Commented:
Hi,  probably let me illustrate more (see attached)

Column B should return the category in Contents lib tab if the verbatim text consists of string in Col A of Contents lib

There are altogether 40 over thousands rows

by the way how to handle not found case,  seems like matrix will not return #N/A,  I wanted to say that if not found all tag to "Others"
mockup.xlsx
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
So right now, you are referencing the whole column references as per what I see in the attached which is different from the formula what you showed in original description.
Create two dynamic named ranges and refer them in the formula and see if that improves the performance.
I created two named range like below...

Category:
=OFFSET('Contents Lib'!$B$2,,,COUNTA('Contents Lib'!$A:$A)-1)

Open in new window


String:
=OFFSET('Contents Lib'!$A$2,,,COUNTA('Contents Lib'!$A:$A)-1)

Open in new window

And then used them in the formula like below...
=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(String,A2)),0))

Open in new window

mockup-v1.xlsx
1
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Christopher YapAuthor Commented:
Hi Neeraj,

thanks for your quick response and I will test it out in my environment on the 40K records,  this is something new that I didn't use it before.

by the way,  can explain a little bit on the OFFSET/COUNTA formula

=OFFSET('Contents Lib'!$A$2,,,COUNTA('Contents Lib'!$A:$A)-1)

=OFFSET('Contents Lib'!$B$2,,,COUNTA('Contents Lib'!$A:$A)-1)

Thank you
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Yap,

As requested by Moderto, I have reposted my posts in your following question....
https://www.experts-exchange.com/questions/29083714/Slow-in-saving-excel-file-using-matrix.html

Please continue in that question as this question will be deleted.
0
Christopher YapAuthor Commented:
Hi Neeraj,

using your two dynamic name ranges method,  and I had tested with the 40,000 rows and it took only less than few mintues to calculate and save,  amazing !!  thanks


can explain briefly the syntax meaning

=OFFSET('Contents Lib'!$A$2,,,COUNTA('Contents Lib'!$A:$A)-1)


Neerai,   how to hand multiple match results,  See row 6



Thank yoiu
0
Christopher YapAuthor Commented:
Neeraj

Attached

See row 6 for multiple match scenario,   what is the method to return in Category,   i.e.    Email,  Word Document

Thank you
mockup-v1.1.xlsx
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You will need VBA to get the desired output because the formula solution will be more complex and considering the amount of data, your sheet once again will become very slow.

With VBA solution, the desired Content columns is populated with values only, no formula in the background. So you can handle the file easily.
In the attached click the button called "Get Content" placed in C1 on Sheet1 to run the code and it will populate the column B with desired output.
I have duplicated the data up to more than 40000 rows to test the code and it returns the output almost instantly.
Since this a macro enabled workbook, you may need to enable the macro while prompted after opening the file.
mockup-v1.1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher YapAuthor Commented:
thanks Neeraj for the great help and I had learned a lot from you.  have a nice day !
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Yap! Glad I could help. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.