Compare cell contents to list

[James Gee]

I've downloaded our corporate Outlook Sent folder to Excel, no problem.

In the CC column, there will be cells with either no name, one name, or multiple names (hundreds).

I just need to compare the information in that cell

Example cell A1 has:

Name 1;

I can very easily use a number of ways, even vlookup, to find Name 1's unit from a list, there are lots of examples, but all seem to be related to one entry per cell.

But cell A2 has:

Name 1;Name 2;Name 3

Can't for the life of me find how to look up all the names. I've tried wildcards, etc. but no luck.

I can't just Text to Columns due to the number of names that can be in a cell.

Thanks.

[alansidman]

How about power query and split the column into rows instead of columns. Here is a sample of what I understand your issue is

Excel 2016 (Windows) 32 bit

	A	B	C
1	Names		Names
2	Alan, Sam, Vic		Alan
3	John, Harry		Sam
4	Larry, Amit, Mary, Joan		Vic
5			John
6			Harry
7			Larry
8			Amit
9			Mary
10			Joan

Sheet: Sheet1

Here is the Mcode

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[James Gee]

Thanks, I'll give it a run when I can and let you know.

[James Gee]

Appreciated, that certainly worked, but I didn't do a very good job of explaining.

My data dump from Outlook gives me this:

Attachment 708322

Ultimately, I need a count for each person, per unit, for TO, CC and BCC.

Started with this one unit:

Attachment 708323

and would like this:

Attachment 708324

As you can imagine, once MGMT sees that I can do it for one unit...they'll want them for all our units, so I would eventually need:

Attachment 708325

Thanks again.

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.