Hi @Pal_Patel ,
Try Using this Formula - “=IFNA(VLOOKUP(A2,C:C,1,0),)”
But Even myself unable to write the formula properly from Write Cell Activity. Probably a bug from UiPath where it is not supporting string constants inside the formula.
Try using VBA for filling the formula.
Happy Automation.!
Thank You,
Gautham.
Hi @Pal_Patel
In your data the cells which are showing -2146826246 value, it is also NA value sometimes for NA it shows this value in excel or in UiPath. To remove this value
Read_LineItems.AsEnumerable().Where(Function(row)Not (row(“AM”).ToString.Trim.Contains(”#N/A") or (row(“AM”).ToString.Trim.Contains(”-2146826246"))).CopyToDataTable
Try using this query.
Hope it helps!! Let me know
Thanks!!
This formula make everything to 0. Which will not work as I need to remove those N/A rows
Hello @Pal_Patel ,
I used the formula in the same workbook you had shared. For the values which are present in column c , i am able to see values being populated and if you don’t want 0 you can add a constant to the ifna like,
=IFNA(VLOOKUP(A2,C:C,1,0),“Value not found in column c”)
Hope it helps.
Thanks,
Gautham.
I is working fine for me. You aren’t doing something right.
Firstly when I use this formula , It converts the #N/A into that -21… number and then if i use again it is not removing that value.
It gives me error. As I sent you the error screenshot. I copied the same formula.
It’s not supposed to remove a value. An Excel formula doesn’t remove values. An Excel formula calculates values. If you want to remove them, then Read Range, Filter Data Table, then Write Range to a new file or sheet.
Did you used through UiPath ? Directly it is working on excel but through UiPath not working. If working for you then pleASE send me sample.
But you didn’t. Your screenshot showed that you used single quotes instead of double quotes.
I tried with filter Data activity as well. But for that n/a nothing is working. IF you can check on your end for the file i sent you.
Error says ‘)’ expected.
You have double quotes inside your expression so it breaks the expression. You have to escape the double quotes inside the expression by using two double quotes.
"=IFNA(VLOOKUP(A2,C:D,2,FALSE),""Not found"")"
But again, writing this formula to Excel, then reading the Excel file back in, then filtering…makes no sense. Just Read Range, use activities to do the lookups and remove the rows you don’t want, then write back to Excel.
But through excel code for UiPath this is not working. It works fine when use in excel manually.
I am not doing all together. I was saying I tried with those activates also nothing worked. By the way your formula is worked here to write notfound. Now How should I remove notfound rows from the sheet ? I tried with filter DT but not worked I dont know why
If it is working fine in excel manually then record macro for same and use invoke vba in your sequence.
Thanks!!
In your read range activity select preserve format in properties panel. After that use filter datatable, In filter datatable activity give the column name and pass both the values “NA” and “-21” and select remove box.
See what output is coming.
If you didnt get the desired output, please share me your input file.
Thanks!
I tried this , not working. I removed other columns and made this sample file.
Book1.xlsx (28.3 KB)
Hi @Pal_Patel
Bro, See the below screenshots I had changed the column name to “Value” which contains na. Also, I had done paste special the entire column so that we get the exact values which is present in the cells instead of Vlookup formula.
Input sheet which contains NA value
Output sheet in which NA rows are removed.
See the Workflow.
Output File
Book1 (1).xlsx (32.8 KB)
Hope these contents help
Thanks!!
So, You apply copy and paste special to value column ? Which just return #N/A text in the cell ? How does that worked ? How you used that in UiPath ? The filter thing I understand. Thanks
