Here is a formula challenge for you.
![How many values are common in 2 lists [homework]](https://img.chandoo.org/hw/formula-size-of-common-values-in-2-lists.png "How many values are common in 2 lists [homework]")
Lets say we have 2 lists of values in A1:A10 & B1:B10
Now, how do you find the number of common values in both lists?
We just want the count, not list of common values it self.
Go ahead and figure out the formula and post your answers here.
Bonus challenges:
- Write the formula if 2 lists have non-numeric values (text for example) ?
- Write the formula if 2 lists are not of same size (A1:A10 and B1:B15 for example) ?
- Write the formula if we have 3 lists (all same size, numeric values) ?
Go ahead and share your answers. I am eager to see what you will come up with.
PS: For some inspiration, check out this forum discussion.
PPS: If you just want to highlight the common values, see this.
4 Responses to “Office 2010 Contest Winners are here!!!”
I while ago I wrote a post on selecting a couple of names from a range via an UDF
I could have been handy.... especially because I didn't win.... lol
http://xlns.lamkamp.nl/?p=14
Sweet! I won! Thank you so much, Chandoo! I'm really speechless! I'll look out for an e-mail from you. Again, I really appreciate it, and I can't wait to fire it up!
Sincerely,
Tom "this one" 🙂
Thank You... Thank You... Thank You... 🙂
Hi,
Don't want to ruin your party.. 😉 but I noticed that when you sort the list A2:B11 (step 2), the RAND function re-calculates the numbers so that they are different and in mixed order again. I had to paste the whole area as values first and then sort to get it to work.
Wonder if the same happened to you because in your list at least Greg has a higher value than Tom 🙂