Finding out whether a surname is contained in another row field

[roomaleuk]

Hi
I am look for a way to find out whether a surname is being used as part of a user name

The file that I am looking at has around 10,000 records and is set up with the following columns
A - Title (Mr, Mrs etc.)
B - Forename (John, Jane, Jenny etc.)
C - Surname (Smith, Brown, James etc.)
D to M - Various pieces of information about the record - address telephone etc

In column N I have a field that contains User Names and the only constraint that has been placed on these is that they must be greater than 5 characters and less than 20 characters. For some reason the user name may include spaces

So in effect - I am look at establish whether the value in column C (Surname) is part of the user name (column N) on a row by row basis

So the surname Smith may be found in the user names
J Smith
J & A Smith
James Smith
John Smith esq

In all of these cases I would like to indicate that the row has a probable match

Whereas in the case of
JSmith
Smithy
A Smithers

These would be marked as 'review' or possible

I have tried various combinations of find, substitute and the like to get around the spaces - but to no avail.

A friend of mine suggested that I look on this forum - and if I couldn't find anything to post a new thread l - so .. here's the posting

I hope that this makes some sense .. and any assistance would be gratefully received as this has become a bit of a quest

Thanks in advance for any help or pointers

Andy

 

[njimack]

Based on the examples you've given, the below should work for you...

Excel Workbook
	C	D	E
1	Surname	Username	CHECK
2	Smith	J Smith	Match
3	Smith	J & A Smith	Match
4	Smith	James Smith	Match
5	Smith	John Smith esq	Match
6	Smith	JSmith	Review
7	Smith	Smithy	Review
8	Smith	A Smithers	Review
Sheet1

 

[roomaleuk]

Excellent thanks - this is pretty much what I wanted - it's also highlighted some other quirks in the data that I have inherited ...

I think I'll be hanging around here a lot moving forward

All the best

Andy