COUNTIF using park of text from a range of cells

I'm using this formula to automate some work. When a list pasted into column N (a range in my formula) and it matches data in column B (a second range in my formula) then change the cell to show "Collected", If not show "Not Scheduled"

Please Login or Register  to view this content.

The problem i have is that the words in the list i paste in and the words in the list it compares them to are similar but not character by character correct. How can i get my formula to search for part of text across a range of cells and if it finds a match the list then change the cell to "Collected".

For example the static list (column B) has "Dublin Road, Bray" but my pasted in list might only contain "Dublin, Bray" and tomorrow could have "Bray Dublin". When i manually input this data i use the find option in excel and type "Bray" and it highlights the cell for me. Its this find functionality i would like to integrate into the formula but i don't know how.

As you can see in the screenshot below only some of the data from column N hilights column D because it matches character by character but a lot of the data wont trigger the D Cells because it doesn't recognise the location.

Can somebody lend a hand?

Sample excel file is uploaded at the end of this post as requested.
excel3.png

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Originally Posted by JohnTopley

You will almost certainly need VBA for this and (unlikely to be 100%) a clear set of rules for matching:

Take

Old Bray Road, Cabinteely in C2

in N2

Cabinteely, Eglinton

in N3

Cabinteely, N11, Centenary

so Cabinteely is "potential" match: but is it correct??

In this case the list i will be posting in will contain "Centenary" in one cell and "Eglinton" in another cell, Since they are both in the Cabinteely location they are guaranteed to be differentiated in the list. They might at times both have the word Cabinteely as part of their characters but they will always at least contain their unique location.

Example:

Centenary, Cabinteely
Cabinteely, Eglinton