Duplicates

Help
1

I have two columns with names as invoice number and date.
I want to check if invoice number is repeated more than once,then the corresponding date should also be same for that invoice number.
Example:Invoice Number Date
1 28-4-20
1 28-4-20

Please suggest some solution for this issue.

2

@Nida_mallik What do you want to perform ? what is the Output that you expect? Is it just a Comparison?

1 Like
3

I want to check whether the each repeated invoice is having the same date of the original invoice and if not this case i want it to update the comment saying “Mismatch” in excel.

4

Updating the comment “Mismatch” in excel is not the problem, But how to identify that there is a mismatch?

5

@Nida_mallik How will you Identify which is the mismatch manually ? :sweat_smile: Can you explain With an example File

6

Example
Two different dates for the same document number.

7

@Nida_mallik Only if all date values are same for a Particular ID, then those rows are Correct else even if there is one different date value then all the rows are Mismatch, is that right ?

1 Like
8

Hi @Nida_mallik , first of all find all the duplicate rows, once duplicate rows are identified then check its adjacent column value.
Steps to identify duplicate rows -

  1. Read excel and get all data into DtAllDataTable
  2. Find all the unique data from DtAllDataTable using select query - DtAllDataTable.DefaultView.ToTable(false, “ColumnName”) and store into distinceDtDatable.
  3. Now, you have 2 datatable 1 with full rows and another with only distinct rows.
  4. apply a for each or selet query over distinceDtDatable and compare with DtAllDataTable using invoice no, if its counts is greater than 1 then it is duplicate row and now check the invoice date weather is same with distinceDtDatable or now.
  5. if your condition matches or not matches, move the rows using add data rows activity and write it to DataTable.

Happy Learning
Sudhanshu

9

Yes. all the rows related to that particular ID is mismatch

10

hi, till step 3 it is fine…after that can you explain in more elaborate manner.

Thanks

11

Hi @Nida_mallik, what you need to do on wards 4th step is you need to find the duplicate invoice no in DtAllDataTable.
For this, apply for each row , on distinceDtDatable and get you will get the first record from distinceDtDatable i.e.

1A invoice no and 30-03-2020 date from 1A and 2A.
Now compare 1A with DtAllDataTable first record i.e 1A so here invoice no and date has been matched and count will be 1

For next iteration again you will get 1A with 30-03-2020 data in DtAllDataTable and here date will not match from distinceDtDatable, so you need to update incorrect in Remarks.

I hope this clarify.
If you need code, lease let me know.

Regards,
Sudhanshu

1 Like
12

@Nida_mallik Can you Check this workflow, Seems like this is what you need, But it works for the Excel present in it . You can change the column names according to your Excel Format and check if it works.
Update_Mismatch_Records.zip (9.2 KB)

1 Like
13

Hi @Nida_mallik , @supermanPunch has coded it just like I’ve explained you.
I hope you got your soluton.

Happy Learning.
Sudhanshu

1 Like
14

Thank You for the solution, Its Working :+1:!!! @supermanPunch and @imsraj

1 Like
15

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