How to compare two columns and find missing data in Excel

[Zaw_Win_Htun1 (Zaw Win Htun)]

Hello Experts,

I want to compare the data in two columns of the excel. If there is mismatched data in the columns, I want to highlight the mismatched data and extract the missmatched data.
I attached the sample excel file for reference as below.
If there is any solution or workaround, please let me know. Thanks.
DataCheck_AgentComission.xlsx (60.9 KB)

[Sanjit_Pal]

Hey @Zaw_Win_Htun1,

You can read the whole datatable and convert it into a list for each column you want to compare.

Convert Column in Data Table to Array

Hello, (From row in portfolio_population_csv.AsEnumerable() Select Convert.ToString(row(“Loan ID”))).ToArray() I am using the above to convert a column in a datatable to an array of values. However, it returns System.Array{} rather than the array of values stored in the column. Please help, thank you.

Have a look on the above thread to convert col to array.

Use the secondArray.Except(firstArray) to get the unmatched records.

Thanks,
Sanjit

[rohith.prabhu]

Hello @Zaw_Win_Htun1

After Reading the Data from Excel use an Assign activity and use the below LinQ to get the Miss-Matched Records from it.

MissMatchDT = (From row In DT1.AsEnumerable
Where(Not row(“PDF Data”).ToString.Trim.Equals(row(“Robot Data”).ToString.Trim))
Select row).CopyToDataTable

Hope it helps, However if you’re looking for something else let us know.

Regards,
Rohith

[Zaw_Win_Htun1 (Zaw Win Htun)]

hello @rohith.prabhu Thanks for your solution. I have got the mismatched data. Actually I want to find the data that is present on the colum1 and not present on the column2. Like the following example:

Is it possible to extract that kind of data? If there is any solution for the workflow, please share with me. Best regards.

[Zaw_Win_Htun1 (Zaw Win Htun)]

hi @Sanjit_Pal How can I convert to system.collections.enumable(of string) to datatable to wirte in excel.

[Sanjit_Pal]

@Zaw_Win_Htun1 (From x in arrVals
Select r = dtResult.Rows.Add(new Object(){x})).CopyToDataTable
use this to convert list or arr to datatable and write it in excel

[Zaw_Win_Htun1 (Zaw Win Htun)]

Hi @Sanjit_Pal If possible, could you please share me the workflow. Best regards.

[Sanjit_Pal]

UnmatchedRecordBasedOnCol.xaml (8.9 KB)

@Zaw_Win_Htun1 , PFA the .xaml for your reference.

[Zaw_Win_Htun1 (Zaw Win Htun)]

Hello @Sanjit_Pal It’s the solution I need. Thanks for your effort. I really appreciate it. Best regards.