Intersection of two sets of values?

Dear Excel Gurus,
Is there a way to isolate an intersection of two sets of values?

Suppose in A1:A5 I have the following five values: a, a, b, c, d. In cells B1:B6 I have a, f, a, f, c, f. I want to find an intersection of the two sets and put them in column C. In other words, I want to have a in C1 and c in C2 since a and c are the only two values that occur in both sets of values (A1:A5 and B1:B6).

I suppose I can create an indicator column and use vlookup with exact match to figure out which of the values that occur in A1:A5 also occur in B1: B6. But I am wondering if there is an easier way to do it: something like a pre-rpogrammed function or a button similar to "Remove duplicates"?


Thank you very much.

Studiosa

Hi,
Yes, here is an example (attached). There are two sets of text strings. I would like to get a third set that contains one instance of every string that occurs at least once in both input sets.

I can figure out how to do that by first copying each of the two sets and pasting them into the same column, then eliminating duplicates. This would result in a "union" of the two sets.
Then adjacent to the union of the two sets I can have an "indicator" column that would use a couple of vlookup commands to determine whehter each text string in the union set occurs in both of the initial sets.

Then I can select those items from the union set that occur in both input sets. That would be the intersection.

I am wondering if there is a simpler way to accomplish this. (I do not know Visual Basic at this moment)


Many thanks.
Studiosa

Hi,
Thank you very much for your suggestion. I tried it and it works! (After I figured out how to input VBA!)

I have not used VBA or macros before. I am wondering if you would be kind enough to explain something in your code:

What exactly does this do:
Range("B10000").End(xlUp).Row
I think it somehow figures out what is the row number is for the last (biggest row number) non-empty row in column B. But I do not know enough to figure out how it does this.
I understood that Range("B10000") specifies cell B10000 (so I guess there is a built in assumption that I will have no more than 10000 rows of data in column B, right?) But I am confused about the End(xlUp).Row I read the help on VBA, but still did not get it.

Again, thank you!
Studiosa

Or use a UDF, see attachment.

Wonderful, thank you very much both for the code and the explanation

though i realize this thread is marked as SOLVED, i could not help but chime in with a non-VBA solution for this intersection problem for those that may be looking for one such. please see attached.

icestationzbra ,
Thank you very much! At this stage in my learning, I find non-VBA solutions much more comprehensible.

Studiosa

thanks, I'll try it