Excel Filtering

rpaexpress
excel-iteration
excel-filter

#1

Hi Expert,
I have a excel file (SS2) containing employee id, Department and its Active/ Inactive status (0 or 1)
i am creating a bot to fetch all the active(1) employees only and paste it somewhere.
I want to skip that particular iteration if bot finds any employee status as inactive(0), So is there any function for this requirement (Which i will implement instead of Expression value after ELSE condition in the workflow in the attached screenshot i.e. SS1)

One more thing,
Current workflow will ignore the row if the status has a null value, But I also want to fetch that row and add to a variable (for eg in variable ‘nulls’ in Recorder variables in SS1) for creating a log file.
But i am unable to fetch that row.

SS1


SS2


#2

Hi @karan_dave,

Try using:

Here a sample script with a sample data file you can try.
Untitled-269.rar (8.5 KB)

I hope it will help.


#3

hi @ashapkina could you please help me in filtering column of spreadsheet on the basis of last 4 ( emplyee id 7,8,9,10) after that i want to count the number of entries and paste it into another excelBook123.xlsx (8.8 KB)


#4

@ddivya you can read the data to a table variable, then use 4 If-conditions to check if the employee id is 7,8,9 or 10. If it is - append the row to another table. In the end, calculate the number of rows in this other table to get the number of entries, reformat this number to string and paste it in the target Excel file.

Here is a sample script
excel-filter.zip (1.1 KB)


#5

thnx for the solution @ashapkina . what will be the steps if the employee id is Dynamic everytime when we downloaded the report.


#6

@ddivya in this case how do you decide what employee IDs you need to check?


#7

@ashapkina on the basis of last employee id cz employee id is incrementing by 1 everytime


#8

@ddivya okay. So do I understand correctly that if in the first file you checked if employee ID was 7,8,9 or 10, in the next file you’ll check if it is 11,12,13 or 14?


#10

@ashapkina when we download the report only last employee id incremented like 7,8,9,10 . in next file it will 8,9,10,11


#11

In this case you can store the 1st employee id you need to check against in a text file, for example, read it at the beginning of the script, and at the end of the script increment by 1 and write back to the file.

excel-filter-updated.zip (13.2 KB)


#12

hey @ashapkina thnx for the previous solution. i have question regarding the same actually when i downloaded the report , then i filter it according to a column (version) but every time when we downloaded report that column (version) entries change . 2.xlsx (9.5 KB)
and another file is emp2.xlsx (8.9 KB)
please tell me how to do it. and latest version value i fetch it from portal and store it in variable .suppose the latest version is 9076.


#13

@ddivya I don’t understand what you need to do. Do you only need to get rows with version 9076?


#14

@ashapkina yes i need to get rows with greatest 4 versions . This latest version incremented by 1 on daily basis


#15

@ddivya you can open the Excel file using mouse clicks, sort the column with the version from highest to lowest, and save the cell with the highest version (E2) in a String variable.


Then you can do the same as in the previous script: convert it to number, and calculate other versions you have to check against (but instead of adding 1 you will need to subtract 1).


#16

@ashapkina could you please send me the whole script which is working


#17

Here it is
excel-filter-updated-new.zip (14.0 KB)
You just need to combine the two scripts above


#18

thnx @ashapkina but the above scripts is totally different from it


#19

Please let em know if it solves your issue.


#20

@ashapkina it shows count 0 instead of 4


#21

@ddivya check the parameters of the Number format actions - if they convert strings to numbers and vice versa correctly - maybe you need to use another format.