Comparing two strings on same row to determine if same record

[Masond3]

Hi Team, 

Need some help / assistance. 

To date, i have extracted all the email domains in our db against a given company and created a unique output (As shown below) 

Example 

Id	Company Name	Email Domain	Total Count Record	Records assigned	%
1111	Mason Ltd	MASON.COM	19	16	84
1111	Mason Ltd	HOTMAIL.COM	19	2	11
1111	Mason Ltd	MASONLTD.COM	19	1	5
222	Alteryx	Alteryx.com	100	98	98
222	Alteryx	Atkeryx.com	100	2	2

For a  next step, i would like to compare Company Name against Email Domain , to determine if the email domain contains the name of the company 

Expected out come 

Id	Company Name	Email Domain	Total Count Record	Records assigned	%	Match
1111	Mason Ltd	MASON.COM	19	16	84	yes
1111	Mason Ltd	HOTMAIL.COM	19	2	11	No
1111	Mason Ltd	MASONLTD.COM	19	1	5	yes
222	Alteryx	Alteryx.com	100	98	98	yes
222	Alteryx	Atkeryx.com	100	2	2	No

I tired doing contains if statement, however it wasnt returning any values, and not sure if a fuzzy match, would provide a good outcome ? 

Looking forward to your suggestions 

[hlee36]

I would take the substring from company name in another field and then use the contain function.

Substring([Company Name],0,FindString([Company Name], " "))

Attached is workflow.

[ed_hayter]

I went for a brute force match with formula on the basis you want to check if the company matches or alternate company name with its suffix like (LTD, LLP etc.)

Attached workflow

[Masond3]

Hi @hlee36 

Thanks for sharing this solution, 

I am encountering a problem, after the "Dynamic rename" the "Company Name" No longer exists. Consequently the first first row of data ( the one is now the row which has 84%) meaning that the formula is unable to calculate, as the "Company Name" is not a variable which can be passes

[Masond3]

@ed_hayter Interesting solution, the downfall of this, it looks like it needs to an exact match. However it could  elimated all the easy match's first ( ie the 100%)

[hlee36]

@Masond3 

Hey, I used the Dynamic Rename tool to take the first row of your data as Field name because when I copied and pasted your data into text input, it copied the field name as part of the data. 

If you already have your field name correctly, you can remove the Dynamic Rename tool.

[ed_hayter]

You could switch my formula at the end to be:

IF Contains([Email Domain], [ALT COMPANY]) then "Yes" ELSE "No" ENDIF to be slightly more forgiving.

Alt company at present is splitting on the first space so there would be issues if a company has multiple spaces in their name

[Masond3]

@hlee36 

Thanks for taking your time to go through this.

So just quick question, for those companies, which only have one word ie Mercer , alteryx, how come the "Company" Field is blank ?

[Masond3]

@ed_hayter  Agree - so trying to work out a way to capture these different scenarios

[hlee36]

@Masond3 

Hey, It was because the formula was finding space and take substring. I updated the formula with if statement so that the company name stays when there is no space.