Comparing values between two columns, with partial matching

[E-Dan]

Hi All,
I'll be glad to receive your help.

I'm a library worker.
I have an Excel file with two columns (A and C for that matter), each with a long list of book titles (approximately 115,000 lines), over 80 percent of the titles overlap and exist in both columns.

I want to compare the two columns and find out which titles exist in both columns, both in A and C. The point is, that the titles can repeat themselves in both columns without a full match (each column was imported from another source),
for example:
Column A, such as "the easy way" may appear in the second column as "easy way, the".

I want to find duplicates of this kind, which exist between these columns, that is, when the same sequence of words exists in both columns.

The possibility of marking by conditional formatting is not suitable for this case because it distinguishes the exact expression, as in a function such as: IF (ISERROR) MATCH (A1, $ C $ 1: $ C $ 5,0)


Is there a way to find a partial match between the two columns in the way I described above?

a slightly complex case.
Thanks in advance for helpers.

[E-Dan]

I will only say that perhaps a solution that will enable the definition of Ignoring symbols when comparing such as space, comma, conjunctions, etc. can help solve the problem.

Thanks again.

[FDibbins]

Hi, welcome to the forum

...The possibility of marking by conditional formatting is not suitable for this case because it distinguishes the exact expression, as in a function such as: IF (ISERROR) MATCH (A1, $ C $ 1: $ C $ 5,0)

Actually, thats not quite true, whatever formula is devised to do what you need, could probably also be used in CF - the problem with CF them becomes that, wit that many records, your file could be slowed down.

To start with, I would suggest that you

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[E-Dan]

Hi FDibbins, thanks

This seems to be the best place for Excel experts.

eventually, I found another figure to base on it instead of the book titles.
but anyway thank you so much for wanting to help, and promise to come back here with any question

Thanks and a successful year to everyone.