How to dupe check data of a column "ID" in sheet1 and sheet2, if data present delete the row in sheet1 , if data not present cut from sheet1 and add in sheet2!

Help Activities
, ,
1

I am having one excel with 3 sheets, In Sheet1 i am checking the data of column “ID”, and checking the same id is present or not in Sheet2 .if “ID” not present i am cutting from sheet1 and writing in sheet2. IF “ID” is already present in sheet2 i want to delete the row in sheet1. Can some one help me to solve this.Thanks in Advance

2

You achieve this by add datarow activity in sheet2 and delete datarow activity in sheet2

1 Like
3

Hi @MitheshBolla
you were able to proceed on this?

1 Like
4

no, as i need to delete data from main sheet when it is present in sheet 2

5

Please share the unput excel

1 Like
6

Actually i have 10 + columns ,
"Receipt number " and "CaseNumber " if both are same then i need to delete

image
image1498×283 23.6 KB

7

Focus on
If data not in sheet2 then add it in sheet2.
Once you are done with this task → delete sheet1 and create a new sheet1 if really need.

This approach will give you the same result

1 Like
8

DublicateDelete.xlsx (13.3 KB)
In this excel 4,5,6 rows are dublicate

9

Have you tried using LINQ Query for this, grouping the rows based on two column values and writing the complete table back in sheet.
I guess i saw this same question in some other post too

10

Daily bot will run , so the data in sheet 2 will append. if there are 100 records today from tomorrow it will write from 101, before writing from 101 i need to check either its present in this sheet2 or not. for this i am not able to write.

11

Can you please explain properly the exact situation?

12

image
image1607×226 28.9 KB

Dupe check is based on two columns .

  1. case number
  2. receiptnumber

in provided screen shot i have hilighted dupe values with yell0w, as case number and receipt number are same,.

but i ignored 2nd, because its the first value with case number and receipt number.

13

(from row in DT1 group row by pn=New With {Key.A=row.item(4),Key.B=row.item(9)} into grp=Group select grp(0)).ToList.CopyToDataTable

Use this linq Query in an Assign activity → DT1 comes from read range of this excel
4 and 9 in row.item represents the column index that we are checking

Output 2 rows → as i can see there are 4 rows which are duplicate 3,4,5,6

1 Like
14

3 is not dublicate as it was the record with different receipt number compared to 2nd and 3rd

15

3,4,5,6 are duplicate entries so they should all be replace with single entry which is the 3rd one.
The above mentioned query will provide the required output, if you are satisfied please mark the reply as Solution.

16

Cross reference

1 Like
17

From this pic , 3rd one should get deleted.

18

@MitheshBolla Try attached workflow

Example.zip (15.5 KB)

Output

Capture
Capture907×121 5.6 KB

Another Approach

  • Before you update the filter data in the specific sheet simply delete rows using Insert/Delete rows activity and then update. In this way you need not to write a code to filter duplicates again for the same sheet
1 Like
19

But when we have multiple dublicates , how to indicate the row count as it will change dynamically.

20

@MitheshBolla Use read range activity and output of this is data table . You can get the rows count using DataTable.Rows.count. The related code you can check in the attached workflow. Don’t consider about any duplicates etc. Remove the all rows except header and write the filtered data( data that you are filtering from the main sheet). If this process doesn’t work then use the attached workflow. it should work !

1 Like