CSE Array with 38,000 lines - 24-hours later it’s still calculating

[Rawlings]

I have a spreadsheet A - AZ, 38,000 lines. Seeking matches for 5,000 in the 38,000 lines. Often I work up to 65,000 lines A - AZ but always multiple lookups. I’ve never tried Index - Match on this size file.

The actual formula looks at six ‘conditions to match’ to return a match.

The simplified version of my formula is:
{=Index($A$2:$A$38000,Match(1,($C$2:$C$38000=$AD2)*($D$2:$D$38000=$AF2),0),4)} of course C-S-E from formula line.

I know the formula works, and it’s calculating line by line but 24-hours later it’s still calculating about a line per second.

Question: will this actually finish or does anyone have a better idea?

thanks!

 

[Rawlings]

I'm not sure of the relevance of that thread since COUNTIFS did not exist then and the comparison were only on one column. My doubt was that as additional column matches were introduced the relative performance of COUNTIFS should improve. That doubt is now supported by the following testing.

I have generated random (numerical) sample data of the scale indicated by this OP.
38,000 rows in 6 columns C:H
4800 rows in 6 columns AD:AI
Expanded the post 7 formula to match data in all 6 columns and copied it down the 4800 rows
Expanded the post 4 formula to match data in all 6 columns and copied it down the 4800 rows
Both columns of formulas produced the same results.
Timed the recalculation of each column individually.
Time to recalculate the 4,800 array formula cells: 112 seconds
Time to recalculate the 4,800 COUNTIFS formula cells: 12 seconds

That certainly is a more optimal finish, either route. My spreadsheet CSE Array was finished calculating this morning when I arrived at work (Monday, 8am Central Standard Time USA). However, I checked it on Saturday morning and Sunday morning to find it was still calculating to 100% every few seconds. Meaning the status bar on the bottom right was blinking "Calculating...45%, 85%, 100%" on a continual run. And my CPU Usage Memory was operating at 99%. I'm using Windows 7 Professional, 8GB RAM I-2600 Processor with 64-bit system. Microsoft Office 365 ProPlus which has Excel 2016. Is my system the reason for the extended calculating time?

 

[Peter_SSs]

So, are you saying it is okay now using the COUNTIFS construct, or not?

 

[Rawlings]

It renders the result based on multiple lookups and if a condition doesn't exists, then it renders "N/A".

It is perfect, simple and fast! I wish I knew this five years ago. Thanks again.

 

[Peter_SSs]

OK, great. I thought in post 11 where you were talking about slow calculations you might have been referring to the Countifs suggestion. All's well that ends well.