I want to filter the data which are blanks from a column and write the records after filtering to the same sheet and I dont want the rest if the data(i.e which are not blanks to be deleted).
I tried but I am getting blank related value first later non-blank values are appending but I dont wanna append.
Spice Money Payments_SEP333’21.xlsx (161.3 KB)
You can use filter datatable activity with below filter condition. It will gives you data with specific column containing empty values. After filtering output this data into new datatable then write your new datatable into excel.
writing back filtered data to the same worksheet with a write range will not delete existing rows.
- old data row count: 15
- filtered data row count: 5
- writing back data: form existing data first 5 rows will be updated with ne data, remianing 10 rows will remain.
So, just ensure that the worksheet is cleansed by following techniques:
- remove and create again the Worksheet
- fill up filtered datatable with empty rows to the same row count of old data, so it will be cleansed.
Instead I can paste the filtered blank values into another sheet but I need to do this on daily basis for each day In a month how can I achieve it?
please describe the requirement in a complete form.
Maybe following is covering your case:
- create the new worksheet by doing:
- write range, Worksheetname: now.ToString(“yyyy-MM-dd”)
Actually its a Bank reconcilation process which BOT has to perform on daily basis for each month.
Daily Bot will get the Input text file converted it into excel after some calculations,
Then the data from the above created excel to be written into a One Recon excel(which contains the data from Sep1 to sep30 in September month and same for other month).
After appending the data to recon excel BOT needs to filter a column(“Contra”) which contains only blank values and non blank data should not consider for the further process.
After filtering with blank columns I need to apply Pivot table using 3 columns once the pivot is done I need to apply the vlookup table with the main recon file .
do you sample values that you can share with us? Marking sensitve data with e.g. *** is also fine for us
When I write the filtered data into the same excel the values which are not blanks are appending below the blanks values but I dont wanna write the value which are not blank.
Store your filter datatable into new datatable and then append new datatable values into your excel sheet.
Any update on this please