Set Intersection

I caught up with an old discussion on Chandoo’s site. The basic question was, “How can I find the number of numbers that intersect in ranges Start1..End1 and Start2..End2?” I had a similar challenge a few months ago in Excel and resorted to Excel’s Intersect method, resulting in this one-liner:

Function IntersectCount(S1 As Long, E1 As Long, S2 As Long, E2 As Long) As Long
' works on whole numbers only,
' parms must be in range [1..max rows allowed by your version of excel]
  IntersectCount = Application.Intersect(Range("A" & E1 & ":A" & S1), Range("A" & E2 & ":A" & S2)).Count
End Function

This works great for non-negative integer ranges with upper bounds < 2^20 (Excel 2007 and later) (great for everyday integers and dates!) I think Rick Rothstein posted a related method, but it did not produce a count.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: