Data match

[Blake 7]

Hi Guys,

I have been asked to match some data -

The data is email addresses, the attached sample is ficticious.

I have a column of legitimate email address (some 201,000) and a column of email address which in every case there are a number of characters missisg from the end of the string. This number varies from 1 - 5

ie [email protected] may appear as darren7@ or darren7@g ect

My task is to match the part email to the full email and see if:

1. The part email appears is the full column
2. if it does then how many times,

I have tried =Left(a2,10) on BOTH columns and then a countif but i feel that there must be a more robust method.

Can anyone suggest anything? thanks alot and happy friday


I have uploaded a sample demontrating the dataset, as you will see the email adds are ficticious!

[NBVC]

Using this formula, I got output in every cell of your sample:

=VLOOKUP("*"&A1&"*",'Positive Full Email'!A:A,1,0)

copied down.

[Blake 7]

Hey N - thanks for this. It really works a treat. As usual , when i don't understand s'thing, or have never seen s'thing before............ i am eager to understand it.

Any chance that you could talk me through this formula - in particular the first wild card, and the use iof the tab name as the table.

cheers

[NBVC]

With certain functions, e.g. VLOOKUP, SUMIF, COUNTIF, SEARCH, FIND (basically the ones that have a comma separating the arguments making up the condition, not the ones that have =, >, <, etc separating them) you can use wildcards.

The * wildcard means any number of characters... and placing the * before and after a specific text string is like saying you want to search for something that contains the string of interest, since the *'s in our function means any number of characters before, and any number of characters after.

I didn't use the tab name as a table, I referenced column A in sheet named 'Positive Full Email' as you know you need to do if you are referencing outside of the active sheet.

[Blake 7]

N- thanks very much for the masterclass!

I didn't use the tab name as a table, I referenced column A in sheet named 'Positive Full Email' as you know you need to do if you are referencing outside of the active sheet.

Yes you are right - silly me!

Off to put B to sleep! cheers mate

[NBVC]

Wish him a great night from Uncle NBVC

[Blake 7]

Canada's MVU!