Compare two excels and update

Help
, ,
1

Hi all,

I am currently trying to make comparison between two excel files. I need to match the value of User ID (Excel 2) with User ID (Excel 1).

  1. If value match, I have to write “Open” on column Status (Excel 1).
  2. Then if we found a match from first condition, everything from the same group of that user ID also I have to write “Open”.
    Example: I found a match for User ID = 18. User ID 18 is on Group A. So I have to write status “Open” to User ID = 1 & User ID = 23 since they are from Group A.

Excel 1:
image

Excel 2:
image

Result should be like this:
image

2

Hi @jenkim,

Please follow below steps,

  1. Read Excel 2 using Read range, take for each loop and get each row item of excel2 and add them into a Array variable.
    2.Read Excel 1 and take a for each loop and get each row item of Excel 1 and take a IF condition and Array variable contains rowitem(“User ID”) variable, then write status as “open” in column c.

Thanks
Latika

3

Hi @jenkim,

Please use Join datatable activity by using inner join which gets you matched crunch of data. Later you can use remove column activity to remove unwanted column and put it in excel file

4

@jenkim
thanks for your clear case description. Kindly note:
Excel2 is different form other sampledata (that is used for explanation along with the red arrows)

it can be solved with a quick done prototype using a LINQ statement and with the help of a group by and the any Method:

grafik

(From d In dt1.AsEnumerable
Group d By k=d("Group").toString.Trim Into grp=Group
Let check = grp.Any(Function ( r ) dt2.AsEnumerable.Any(Function (x) x("User ID").toString.Trim.Equals(r("User ID").toString.Trim )))
From g In grp
Let ra = New Object(){g(0),g(1), If(check, "Open",g(2))}
Select dtResult.Rows.Add(ra)).CopyToDataTable

we group the data by the group col
for each group we check if any group User ID is found in dt2
based on this check it can be decided if the status is open or not for the groups
finally we iterate over all group members and based on the check the status open is or not

find starter help here:
GroupBy_SetGroupStatus_ByRefListMatch.xaml (10.6 KB)

4 Likes
5

Thank you so much for your solution. Really helped me a lot.

6

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