Posted: 9 Nov 2017 0:31 EST Last activity: 20 Dec 2017 5:47 EST
How To Find Dulpicate Row in Excel And Update A column with Status As its Duplicate
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.
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.
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 sameaccount number is found and and sameto date is also found but the from date is different then it is not a dulpicate record.
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.
any other way to this process ?
I have attached the screen shots of my automation.