How to Search Comma-Separated Content in a Cell against a List

JMKnapp

New Member
Joined
Mar 11, 2015
Messages
6
Hello, everyone. Thanks for taking the time to read my post and to try to help.

I have one column of comma delimited names (ie, "Bill, Sue, Carla-S, Dave") that I would like to search against another pair of columns -- one that has a single name per cell, and its neighboring column with an associated project value.

My description may not make it very easy to visualize, so here's an example:

ABCDEF
1TeamValueSingle NamesResult
2Bill, Sue,
Carla-S, Dave
ABill
3Mark, Mary,
Oliver, Randy
AAdam
4Adam, Brad,
William
BSue
5Alex, Beth, Sue,
Adam, Amanda,
Bryan, Joe, Catherine,
Betty, Bill, Marco
BCarla-S
6ADave
7BMark
8BMary
9AOliver
10BRandy
11BBrad
12AWilliam
13BAlex
14BBeth
15AAmanda
16ABryan
17AJoe
18ACatherine
19BBetty
20AMarco

<tbody>
</tbody>

My goal here is to comb the comma delimited names in column A for the value from column C that corresponds to the associated single names in column D. For instance, if my results display in column F, then F1 would catalog values of A, B, B, A. The order of the values listed in F1 wouldn't matter (A, B, B, A or A, A, B, B etc.) as long as the amount of As and Bs is correct.

In my real-life work, column A is in a different worksheet than columns C and D (but are in the same workbook). I assume that any formulas discussed here will be easy enough to modify across sheets, but I've presented by example as-is for simplicity's sake.

I'd rather do this through a formula than VBA code, but if VBA offers the most logical route to solution then I'm more than willing to head down that path instead.

As an added twist, the file that I'm working with needs to stay in 97-2003 .xls format.

If I've neglected to provide any info that would be helpful, please let me know.

Any assistance is greatly appreciated.

**EDIT** It's probably worth mentioning that there's no set number of or limit to the names that could be in the cells of column A. A cell could include a single name (with no comma), three delimited names, 11 delimited names or what-have-you.
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,311
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top