Help in Excel column data matching

[Learner007 (Uipath Learner)]

Hi All,
i have a scenario that i need to look company name from SHEET1 and look that Company Name in SHEET2 and i want to write those in OUTPUT sheet.
My code is working fine but there is an issue some company names having additional special characters because of that I’m unable to match it. i can’t use contains because if another company also contains similar kind off name it will take that.
I want to work with special characters how can i.
I’m attaching my code below for reference.

CompanyNameMatch.zip (11.3 KB)

[Yoichi]

Hi,

Can you try the following expression?

dt_Test =  Sheet2_DT.AsEnumerable.Where(Function(x) System.Text.RegularExpressions.Regex.Replace(x("Company Name").ToString,"[^\w\s]","").Trim.Equals(System.Text.RegularExpressions.Regex.Replace(row("Company").ToString,"[^\w\s]","").Trim)).ToList()

Regards,

[Learner007 (Uipath Learner)]

Hi @Yoichi thanks for the code but when i run my code it is not printing all matching rows. It is skipping 1 record - **Universal Logistics Holdings Inc.
**
Did i made any mistake in code.

[Yoichi]

Hi,

Can you share your expected output?

Regards,

[Learner007 (Uipath Learner)]

@Yoichi If my Sheet1 Company Name matches with Sheet2 Company Name. I need to pick all the matching records from Sheet2 and write in Output sheet.

Company Names Match.xlsx (10.4 KB)

[Yoichi]

Hi,

In Sheet1, there is duplicated rows as the following.

If you want to handle them once, it’s necessary to get rid of duplicated row in advance.

 Sheet1_DT = Sheet1_DT.DefaultView.ToTable(True,{"Company"})

Regards,

[Learner007 (Uipath Learner)]

Hi @Yoichi Still i’m not getting all records. Even it is not writing all records of **Universal Logistics Holdings Inc.
**

[Yoichi]

Can you check the following sample?
(Workbook activities are used because excel is not installed in my current environment)

CompanyNameMatchV2.zip (10.7 KB)

[Learner007 (Uipath Learner)]

This is working perfectly. Thank you @Yoichi