How to compare two columns in excel to get the sub string from one column?

Help
,
1

In excel I have to compare column “Company” with the column “Name” . If the company col contains any of the part of the string from Name column for that particular row, I have to delete the row. I am not able to figure out the if condition to be put for this check. Please provide me the some inputs on this. It’ll be great!

Regards,
Komal

2

@Komal_Joshi, You can also use LINQ to select rows where value from “Company” Column dont contains value from “Name” column,

Datatable dtFinal = (From row in datatable.Select() Where not row("Company").ToString.Contains(row("Name").TOString) Select row).ToArray().CopyToDatatable()

Regards,
Dominic :slight_smile:

3

@Komal_Joshi

Can u upload the excel sheet. I think your requirement is if the column company value contains a part of the Name value we have to select the row right.

eg:
Company, Name
abcd , hfg abc
fdgs , ghfb

now we have to select the first row right.
If Iam wrong please let me know.

Regards,
Mahesh

4

Thank you. :slight_smile:

5

Yes. You are right. Further need to delete the entire row containing similar word.

7

ok. Thank you :slight_smile: Can you tell me how to extract row in excel and print row by row ? I want to print serial number from 1 to 100 in excel file under Sr. No column.
Regards,
Komal

8

@Komal_Joshi
Try this
(From p in datatable.Select()
where Not p(“Name”).ToString.Split({" "},StringSplitOptions.RemoveEmptyEntries).Any(Function(d) p(“Company”).ToString.Contains(d))
Select p).ToArray.CopyToDataTable()

Regards,
Mahesh

1 Like
9

Hey Thank you @Dominic and @MAHESH1 your solutions worked fine but when I am using this condition on different excel, there is an error displayed as “Assign new dataTable to store valid rows : The source contains no DataRows.” I am unable to know how to resolve this now!
Can you kindly suggest any other way on it? your help is appreciated.

Regards,
Komal

10

@Komal_Joshi
Your datatable might be empty. check once.

Regards,
Mahesh

11

No actually it’s filled with data currently. The condition works fine in the case. For few excels it’s not working. I am not exactly aware of the cause.

12

@Komal_Joshi

I think your datatable is not having the values which satisfes the condition.
So if you try to convert empty array of datarows to datatable it will throw exception.

Before converting to datatable just check the array count>0 in if condition
If it is greater than zero then convert to datatable by using copy to datatable activity

Regards,
Mahesh.

13

OK. Thank you @MAHESH1 :slight_smile:

14

Hi
how to modify this query if these two columns are in two different datatables.
My query is that i have two columns col A in dt1 and col B in dt2. I need to compare both the columns and find out which rows are not matching.
Can you modify this query accordingly?

15

Hi @Abhilash_Bhanwal,
This topic will help you for what you are looking for…

Regards,
Komal Joshi

16

Hai guys BRS statement.xlsx (10.7 KB) textexcel.xlsx (8.9 KB) i want to compare column from one excel sheet to another excel sheet column substring and find missing or unmatched values
please help me out
here my excel sheets and columns highlighted in yellow colour