Excel Filtering

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

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

@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?

@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

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)

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.

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

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

@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).

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

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

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

1 Like

Please let em know if it solves your issue.

@ashapkina it shows count 0 instead of 4

@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.

thnx @ashapkina it worked bt what will i do if like in this file the largest four is 9076, 9075, 9073,9072 emp2.xlsx (8.9 KB)
so how this script will work . i.e 9074 is not there

hey @ashapkina i have an excel file i want to filter it by column DAT like latest is 9096,9095,9054,9053 so i want only first three 9096 ,9095 ,9054 whether theses three available or not bt i want these three counts and want to paste it in another cell.
1.xlsx (9.9 KB)

Here is a sample script that adds all rows with 4 largest versions to a new table.

filter-tables.zip (1.3 KB)

Note that the column with the versions has to be filtered from the largest to the smallest version for the script to work, so you will need to add actions to filter it at the beginning of the script.

hey @ashapkina how to save excel file into .csv file

There are no built-in actions for it. You can open the excel file on the screen and save it using mouse clicks and keystrokes: file - save as - browse, etc.

Here is a similar example from Word