Any clever solutions to speed up a 50,000 row MATCH formula?

[d0rian]

I have a data set of 50,000 text entries in column A. Every day a few hundred entries are added to the data set, and I paste the NEW set of entries (e.g. let's say a total of 50,275, which includes both the 50,000 from the prior day and 275 new ones) in column B.
I want the fastest way to identify the new 275 entries (which exist in column B, but not A).
The simplest method I know of is to paste a simple MATCH formula in column C, e.g. =IF(ISNUMBER(MATCH(B1,A:A,0)),"","n")
But that takes 10-12 seconds to run on 50,000+ rows.

Can anyone think of any way to accomplish this faster?

 

[GraH]

Sort column A ascending and apply a binary search.

 If( Vlookup(B1,a:a,1)=B1, "", "n").

 

[jasonb75]

A slight correction to the syntax of @GraH's suggestion.

=IF(LOOKUP(B1,A:A)=B1,"n","")

 

[d0rian]

Wow, OK so both of those options work in approx the same time: < 1s. (I assume they're essentially equivalent, but that LOOKUP is slightly preferable for optics / simplicity, since VLOOKUP unnecessary when it's a single-column lookup, and has more parameters.)

MUST col A be sorted alphabetically for this to work properly?
(EDIT: answered my own Q by testing with reverse alpha sort on col A and finding that that does indeed break this...so need to better understand what's going on with this formula, and why sorting is a pre-requisite)

Also, just why does this cut 95% off the calc time...I haven't used LOOKUP in ages, having thought that INDEX/MATCH was far faster/preferable...but why in this instance is LOOKUP so much faster, since there's tons of other places where i want to deploy this...

 

[GraH]

MUST col A be sorted alphabetically for this to work properly?

Also, just why does this cut 95% off the calc time...I haven't used LOOKUP in ages, having thought that INDEX/MATCH was far faster/preferable...but why in this instance is LOOKUP so much faster, since there's tons of other places where i want to deploy this...

Yes, else the binary lookup does not work correctly.

This is why Linear Search vs Binary Search - GeeksforGeeks

 

[d0rian]

Yes, else the binary lookup does not work correctly.

This is why Linear Search vs Binary Search - GeeksforGeeks

Fascinating and clever. Like the Price is Right clock game strategy in Excel form.
Are LOOKUP (and V- / H-LOOKUP) the only functions that apply the binary approach? Or can it be incorporated into MATCH and other functions as well?

 

[jasonb75]

There is also Xlookup (office 365 only) and MATCH / XMATCH (again, office 365 only).

With these functions you can also reverse the sort order, the office 365 functions have a more options, although I don't remember all of them off hand.

 

[d0rian]

There is also Xlookup (office 365 only) and MATCH / XMATCH (again, office 365 only).

With these functions you can also reverse the sort order, the office 365 functions have a more options, although I don't remember all of them off hand.

Maybe I'm misunderstanding, but you mention the MATCH function (which of course exists in all Excel versions) as one that can incorporate binary search methodology....but that's of course the super-slow formula in the OP I started out with that I presume uses linear logic, by default at least. Is there a way to force MATCH to use binary logic? Or did I misunderstand your reply?

 

[d0rian]

Yes, else the binary lookup does not work correctly.

This is why Linear Search vs Binary Search - GeeksforGeeks

Does the "ascending" sort required mean:
i) smallest to largest for numerical values, and
ii) A>Z for text?

IOW, I assume binary can be used for either numerical or textual data sets, presuming they are sorted by low-to-high / A>Z, respectively?

 

[GraH]

Numbers sort before text. So it works on a combination of numbers and text or even mixed strings.
If I'm not mistaken sorting is on the asci code.