Comparing and consolidation two excel sheets

Hello everyone,

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.

Can someone please help me.

I’m attaching both the excel files below:
test_data_1.xlsx (17.8 KB)
test_data_2.xlsx (15.2 KB)

@Karthik23

Please follow the steps

  1. Read excel 2 into a datatable dt
  2. Now use exce file activity and give excel1 as input
  3. Use a for each row in excel inside use excel file activity
  4. 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
  5. Now use if condition with filtereddt.rowcount>0
  6. On then side use assign activity with currentrow.ByField("Matched_Info") = filtereddt.Rows(0)("mapping comments").ToString
  7. 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

Hope this helps

Cheers

Hi @Anil_G , I couldn’t get your solution.

Could you please explain more in detail.

Thank you.

@Karthik23

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

cheers

Hi

Can you try the following sample?

Sample20230519-5L.zip (32.3 KB)

Regards,

1 Like

@Yoichi Thank you so much for the solution.

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.

Can you please help me correcting the code.




RowbyRow_Comparison.zip (33.2 KB)

Hi,

Can you modify it as the following step?

First move second IF block after ForEachRow as the following image.
Then modify Excel2 to Excel1 in FormatCells activity.

Regards,

If I move the second If block out of second for each then I will not be able to write the data of second excel file in first excel.

There is a typo mistake in my code.

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?

Regards,

Yes, I want to compare them in same row number.
comparing row1 of dt1 with row1 of dt2, row2 of dt1 with row2 of dt2…

Hi,

All right. In this case, it’s unnecessary to use double loop. Can you try as the following?

Condition is

First("Record").ToString.ToLower.Trim = dt2.Rows(idx)("Record").ToString.ToLower.Trim AndAlso First("Field").ToString.ToLower.Trim = dt2.Rows(idx)("Field").ToString.ToLower.Trim AndAlso First("Segment").ToString.ToLower.Trim = dt2.Rows(idx)("Segment").ToString.ToLower.Trim AndAlso First("Element").ToString.ToLower.Trim = dt2.Rows(idx)("Element").ToString.ToLower.Trim AndAlso First("Mapping comments").ToString.ToLower.Trim = dt2.Rows(idx)("Mapping comments").ToString.ToLower.Trim

RowbyRow_Comparisonv2.zip (34.0 KB)

Regards,

1 Like

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.

Hi,

How about the following?

RowbyRow_ComparisonV3.zip (34.1 KB)

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.

How do you want to handle this? In the above sample, it skips if there is no rows at same row number in dt2.

Regards,

1 Like

Thank you so much @Yoichi, The output is as expected.

1 Like

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