Comparing cells in 2 datatables

Hello

My process is this - comparing 1 datatable with another. The datatable is only one column in an Excel spreadsheet as that’s all that is needed. DT1 will have the cell as 005587766 (for example), and DT2 will have the cell as 5587766 so I’ve .Contained it rather than .Equals. Any cell that is missing will be written back to a spreadsheet-

The first thing I do is get the data from Excel, put it into a datatable and do a row count:
image

Straight away I can see there are 68 rows missing from "file to compare but when I run my process, 118 lines come back as “Missing”, though obviously they all aren’t missing.

This is my process:

For reference, intLastCellNoStaffList is “Rows in staff list” noted at the top.

Can someone please tell me where I’m going wrong, and how to fix it?

Thanks :slight_smile:

@Short

as per what I understand you can actually use a filter datatable and check the filtered count if the value exists or not…you need not use loop counter

inside first loop

filter datatable or use linq dt2.AsEnumerable.Where(function(x) x(0).ToString.Trim.Contains(currentrow("FirstTableColumNToBecompared").ToString.Trim)).Count>0

this will be true if atleast one match found else false

cheers

Hi @Anil_G

Thanks for that :slight_smile: Is this the correct filtering? (I’ve erased the “Column” name but it is there)

As when I have ran this, the missing amount changes to 335. I’ve changed the datatable order and the missing amount drops to 268 but still nowhere near the correct amount of 68.

I’m not sure how to use the linq, can you show an example of it please?

Thanks

@Short

just use the above expression in the if condition field…with proper columns names …and datatable variables]

you can as we use this directly in if codniton dt2.AsEnumerable.Any(function(x) x(0).ToString.Trim.Contains(currentrow("FirstTableColumNToBecompared").ToString.Trim))

cheers

1 Like

Hi @Anil_G

Thanks for this, I’ve added this to my process and ran it but it’s adding the first number in the second dt to the spreadsheet 335 times, because when the number is compared to the first row in the first dt and it isn’t there, it’s adding it, compares it to the second row, isn’t there so it’s adding it etc

Any ideas?

Thanks :slight_smile:

@Short

Can you show the process after change…you need only one loop now and not two

cheers

1 Like

Hi @Anil_G

Ohhhh of course, what a fool I am! Thank you so much for all of your help and patience, it’s working perfectly now :smiling_face_with_three_hearts: :partying_face:

1 Like

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