Matching partial strings in 2 columns of text data?

[MontyJ]

I have two columns of string (text) data, A and B.

I want to check for the possibility of a 'probable' match of cells in Col B matching any cell in Col A.

Example of contents-
------------------------------------------
Cells A1:A5 example:

A Tale Of A Tub - Jonathan Swift
A Tale Of Two Cities - Charles Dickens
A Thousand Splendid Suns - Khaled Hosseini
A Thousand Suns - Alex Scarrow
A Time To Betray - Reza Kahlili

Cells B1:B2 example:

Thousand Splendid Suns, A`Khaled Hosseini
A thousand suns - Scarrow, Alex
-------------------------------------------

In this example, you can see that the two entries in Col B are 'probable' matches (duplicates) to two of the entries in Col A. The ultimate goal is to add non-duplicate entries in Col B to Col A, so I need to somehow flag or otherwise identify those that are duplicates.

I need some way to eliminate the laborious manual searching I now do to determine whether a given entry in Col B is 'probably' a match for a given cell in Col A. Col A actually has over 25,000 entries, while Col B may have 100 or so when I check a new 'batch' of entries to be added.

I have looked at many 'examples' of using INDEX with MATCH, SEARCH, LOOKUP and FIND examples, and tried some of them for the example above, but so far I have no results even close.

As the examples show, the book title may have different case, the "The" and "A" at the beginning of a title may be put at the end after a comma, and the author's name may be reversed with a comma. There are other format issues, but these are the most common.

Is there a way to setup and search within a string array for a match of more than one word? If it comes to it, I could enter my search terms manually. For example, using the B1 entry above I could manually enter:

C1= "Khaled"
D1 = "Hosseini"
E1 = "Splendid"
F1 = "Suns"

If I could then search within the A1:A5 array for a cell that contained all those keywords in any order, with any case, then I know I have a probable match! But I am lost on how to do that, or even if it can be done. This would still be more time consuming than I would like, but....

I would prefer to simply paste in my Col B list of entries and perhaps adjust the B1:B? array range and go, but perhaps that is asking too much of Calc?

In either case, any pointers on how I can make this work?

Thanks!

Monty

[Robert Tucker]

[MontyJ]

Robert,

Thanks for those tips! It will take me a while to digest your code, but hopefully it is something that can be expanded on.

In your example, I would need ALL of the results in E1 thru E4 to be "True", as all of those words are found in A3. But your layout would be a good way to easily see the 'probability' of a match if say 2 or 3 of the keywords matched (were "True").

As for "fuzzy logic", it might be helpful, but since I am searching for specific, whole words, I would think pursuing "string searching" would be a more practical approach using Calc. Yes the words are 'jumbled up', but the spelling of those words is reliable enough to give me a high probability of a good "hit" if we could make all 4 of those keywords in this example = "True"!

Do you see any way to do that with your approach?? I will dig into it later today, as I have an urgent dental appointment in few hours to solve a problem that is keeping me from thinking all that clearly, LoL.

Again, thanks!

Monty

[Robert Tucker]

[MontyJ]

Robert,

'Fhanx 'or ah 'elp!, LoL. That is what I sound like if I verbalize "Thanks for the help" with my lip feeling about the size of a basketball!

Now I get what you are doing! I had some weird results but found the problem to be a typo in the last search term; it should have been (SEARCH($C$4;A1;1)), not (SEARCH($C$1;A1;1)).

I then simply did a SUM of Col E and know how many dupes are in the list. Next step will be to VLOOKUP of all the "True" hits and return me the row number(s) so I don't have to scroll down the entire list to find them all! Actually a whole array (pun intended!) of options now present themselves, thanks to your help.

Monty

[Robert Tucker]