Which data set has the best overlap with a random sample

[snomis]

Hi. In cells A1:A100 I have a list of numbers from 1 to 100. In column B, is a list of first names separated by a space and comma. For example, cell A2 has the value 2 and cell B2 has the text John, Paul, Andrew, Zach, Tom, James. I have 30 random names in cells D1:D30 and what I would like to do is see which cell in column B has the greatest overlap with these 30 names, which has the second best overlap, and so on. Is there a quick way of doing this? Many thanks in advance.

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Tony Valko]

Try something like this...

Data Range

	B	C	D
1	John, Paul, Andrew, Zach, Tom, James	2	Cindy
2	Fripper, Pat, Grimble, Plinka, Nurmy	3	Fripper
3		------	James
4			Skonce
5			Karen
6			Grimble
7			Paul
8			Loupy
9			Plinka

This formula will count how many names from the list in column D appear in the cell.

Entered in C1 and copied down:

=SUMPRODUCT(--ISNUMBER(SEARCH(D$1:D$9,B1)))