Compare values from different excel

Assume I have two different excel. Below is the first excel,

image

And my second excel as below:

image

What I wanted to do is that, I will get the same ID between the two excel, then I get the “Status” value from first excel, and just write it back into the second excel, in “Status” column.

The output as below:

image

Hi @aqiff ,

Just to clarify, do you want only those IDs which are present in both Excel Files, or do you want just the statuses from the First Excel to be pushed into the Status Column in the Second Excel File?

Kind Regards,
Ashwin A.K

So Basically you want Id’s from first excel sheet with status eg .Id 123456 ,and id 346776 whose status is error in second excel sheet right ?

Yes, I only want the ID which present in both excel files, if the status is “Error”, I just want it to rewrite “Error” in “Status” column.

Hi @aqiff ,

Got it, then could you please validate whether the extracted results are accurate?

image

(From r1 In dt1.AsEnumerable()
Group Join r2 In dt2.AsEnumerable()
On r1("ID").ToString.Trim Equals r2("ID").ToString.Trim Into gj = Group
From g In gj.DefaultIfEmpty()
Where Not IsNothing(g)
Select r1).CopyToDataTable()

CompareValuesTwoExcelStatus.xaml (13.0 KB)

Kind Regards,
Ashwin A.K

I want to do a checking if the id is present in both excel, and if the status is “Error”, if yes, i want to write it in the second excel

This is correct ! But i want the other values from the dt2, which “Status” is emptyy

Hi @aqiff ,

Could you please tell me if you want all the rows from Both Excel Files or only rows from the Second Excel File?

If its the latter, then this might be what you were looking for:
image

Declare a Dictionary(Of String, String) →

dt1.AsEnumerable().ToDictionary(Function(k) k("ID").ToString.Trim,Function(v) v("Status").ToString.Trim)

Then assign this to a new DataTable →

(From row In dt2.AsEnumerable()
Let status = If(dict_statuses.Keys.Contains(row("ID").ToString.Trim),dict_statuses(row("ID").ToString.Trim),"")
Let ra = New Object(){row("ID"),row("Country"),status}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

CompareValuesTwoExcelStatus_v1.xaml (13.1 KB)

Kind Regards,
Ashwin A.K

Only rows from the second excel. Wow, are you using linq for this ?

Yep I use LINQ most of the time since its fun to play with!

Could you check if the results are as expected?

Kind Regards,
Ashwin A.K

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.