How To Find Dulpicate Row in Excel And Update A column with Status As its Duplicate
Hi Team,
An excel having clolumns bank account number , From Date , To Date and Status
if there is any duplicate vallues occured like Three columns bank account number , From Date , To Date Are Same ( See Below Example) then we need to update the status column as Duplicate , If two columns are same and and Third columns is different then its not a duplicate value if and only if the three columns are same then only we need to update the status.
Bank Account Number From Date To Date Status
123456789 22-08-2006 01-10-2007
123456789 03-07-2010 05-09-2010
123456789 22-08-2006 01-10-2007 Duplicate
123456789 22-08-2006 01-11-2007
423823157 30-09-2015 09-09-2017
123456789 22-08-2006 01-10-2007 Duplicate
423823157 30-09-2015 09-09-2017 Duplicate
423823157 30-09-2016 08-09-2017
Thanks In advance
Amarnadh
Accepted Solution
Pegasystems Inc.
US
Accenture
IN
Below is the screenshot for the above question
Accepted Solution
Pegasystems Inc.
US
There are a number of ways to do this. For some tips on working with Excel see this post.
One way would be to export the data to a datatable and then use a dataview to look for duplicates. This is a little more complicated and you will need to know how to use the components (google search for DataTable and DataView).
Another way would be to read one row at a time (using GetCellValue) and store the results for the row in a lookup table. Before storing in the lookup table check to see if the bank account is already present in the lookup table. If it is present, then check the other columns to determine if this is a duplicate.
Accenture
IN
Hi Jeff,
Is it possible to compare three columns at a time , i mean my concern is if and only if three columns are same then only we need to update the status , for instance if same account number is found and and same to date is also found but the from date is different then it is not a dulpicate record.
Acc Number From Date To Date Status
A B C
A B D
A C C
A B C Duplicate
Please look at the attached screenshot.
Regards,
Amar
Accenture
IN
Hi Jeff,
I have tried somthing different but struct at updating the Status field :
I have taken 1st row in one loop and and taken those three values ACCOUNT NUMBER , FROM DATE , TO DATE , And using StringUtills concatinated those three values and saved into a string
then i have taken a 2nd loop and done the same for 2 nd row and then tried to compare those two values if success then i should update the STATUS as duplicate but i am unble to insert the data into that cell . Can you help me to do this.
or
any other way to this process ?
I have attached the screen shots of my automation.
There are a number of ways to do this. For some tips on working with Excel see this post.
One way would be to export the data to a datatable and then use a dataview to look for duplicates. This is a little more complicated and you will need to know how to use the components (google search for DataTable and DataView).
Another way would be to read one row at a time (using GetCellValue) and store the results for the row in a lookup table. Before storing in the lookup table check to see if the bank account is already present in the lookup table. If it is present, then check the other columns to determine if this is a duplicate.