I’m currently working on a excel consolidation. where I have to compare the data of columns “Element”, “Segment”, “Record”, “Field”, “Mapping comments” of both the files.
I need to compare the each row of excel 1 with every row of excel 2. If the data is matched then I have to update the column “Matched_info” of excel 1 with the data of column “Mapping comments” of excel 2.
Else I have to update the column “Matched_info” of excel 1 with the data of column “Mapping comments” of excel 2 but with red font color so that it can be identified as a mismatch.
Note: data found in row1 of excel1 can be found in any row of excel2. Hence I need to compare each row of excel1 with every row of excel 2.
I had already tried few ways but couldn’t get the expected output. And I tried using VB.net code but I’m getting lot of validation errors.
Now use exce file activity and give excel1 as input
Use a for each row in excel inside use excel file activity
Inside the for each row in excel activity use a filter datatable activity and give input as dt(read in step1) and output as filtereddt…and configure filter to filter required columns in exce”l2 with data crom excel 1.for example "Segment" on the left side and equals symbol at the centre and right currentrow.ByField("Segment").StringValue …similarly configure other filters
Now use if condition with filtereddt.rowcount>0
On then side use assign activity with currentrow.ByField("Matched_Info") = filtereddt.Rows(0)("mapping comments").ToString
On else side use set cell color and set the color to read…for range use currentrow.ByField("match_info") and set the value to not matched
Basically I am reading the second excel…and looping the fist excel each row and filtering the second excel data to check if data is present and then if present writing the comment …if not present then setting color in excel to red and setting value as not found
I another scenario I even have to compare the data row by row. below is the flow of the code I worked on. However I’m not able to get the desired output. only first row of the data is getting updated on each iteration.
The conditions to compare the data remain same. However while comparing the data if there is mismatch I have to update the value of the column matched_info of excel1 with value of column mapping comment of excel2 in red font color.
if dt1’s record matches dt2’s record, we can write dt1’s record because they are same.
If there is no record in dt2 which is same as dt1’s record, we cannot identify record in dt2 which should be written, I think.
Or do you want to compare them in same row number?
Hi @Yoichi , comparison condition is working perfectly fine. However I want to write the data of column mapping comments of excel2 in excel1. However in the code we are using single for each and writing values of first excel in first excel which is not expected.
I used second for each in my previous code so that I can write the values of second excel in the first excel
And the number of rows in excel files are different and I’m not sure how to handle the exception:-If: There is no row at position 155.
