How to remove duplicate rows and empty rows if my values starts from column F11 onwards in between of excel

[tkiran (Kiran T)]

Hello,
Can anyone pls help me,
How to remove duplicate rows and empty rows only in column “F” where I am using read column activity as shown in the image and my values start from row F11 onwards.

[NIVED_NAMBIAR (NIVED N)]

Hi @tkiran
You can try this method as well

1. Read the excel and store in dt1 (read from desired range)

2. Now use the assign activity

dt2= dt1.AsEnumerable().GroupBy(Function(r) r(columnname).ToString).Select(Function(g) g.First).Where(Function(r1) r1(columnname).ToString.Trim<>"").CopyToDataTable

3. Now write the dt2 to the excel sheet.

Attaching a sample excel file for this
Main.xaml (12.7 KB)

Regards,
Nived N
Happy Automation

[tkiran (Kiran T)]

Hi Nived,
Thank you for your Reply,

I can get the correct output if my write range is in another sheet. and in output sheet also i am getting other columns also which i do not want

Actually I need to write in the same sheet but when I write in the same sheet in row “F10” duplicate rows are not removed.
can you pls help me how to write in the same sheet.

[NIVED_NAMBIAR (NIVED N)]

Hi @tkiran
before writing the datatable to same sheet, use Delete Range activity to delete the required rage [in ur case, it is excel table] and then write it to it using write range.

Regards,
Nived N

[tkiran (Kiran T)]

Hi Nived,
I do not want to delete anything just I want to remove duplicates and empty rows in “sheet 1” in column “F” only

[NIVED_NAMBIAR (NIVED N)]

Yes @tkiran

Actually delete range will remove the darat and write range will add the updated data

Try that please

[tkiran (Kiran T)]

Hi Nived,

I was confused what to give in range field so i jus tried “F9 : F1000”
Finally got it thank u so much

[NIVED_NAMBIAR (NIVED N)]

Hi @tkiran

You can give the entire range of excel data from where the datatable data is stored