Compare two excels and update

Help
, ,
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