Enter data in new excel row for duplicate detection formula

Help Studio
, , ,
1

Hi guys,

I’m new to UiPath and I’ve scoured the forums to find a solution for my problem but can’t seem to find anything that fits my issue.

I am extracting a subject line from Outlook (it will be in the form of a 7 digit number) and I have a variable where that 7 digit reference number is stored.
Now, I am trying to check whether the 7 digit reference number given has been provided to me before (aka it’s a duplicate) so the easiest way I thought of to do this is create a data validation formula in Excel so it checks all the rows in 1 column to see if there have been duplicates of the 7 digit reference.
I did this with this formula in Excel: =COUNTIF($A$2:$A$500,A3)= 1
In order for this formula to work effectively, I need the 7 digit reference number to be entered in a new row each time it’s taken from the email subject line (all of this is in a For Each loop).

In Excel Application Scope, when I used write cell with the Counter +2 technique, the struggle was that it just copied the reference as opposed to type it in so it never detects the duplicate entries. I need the bot to press ‘Enter’ so the excel formula can work and throw an error message box when there is a duplicate (see image).
So then I tried to use Type Into + “[k(enter)]” (see image of my sequence) and got it to recognise when there is an error but I don’t know how to get it to type into a new row each time. Most of the time, it just overwrites the existing data and sometimes it looks like it’s typing the reference number in but then it disappears…

Can anyone help? Is there a simpler way to carry out these activities?

excel screenshot
excel screenshot875×231 13 KB

duplicate check screenshot
duplicate check screenshot580×561 46.6 KB

2

Hey @sho1,

I think the problem is you’re trying to use Automation with excel formulas, and that’s causing you more issues than you need, because of you new’ness to UiPath.

I would suggest getting your 7 figure variable, Then in a for each loop get the row info of the column in which the duplicate may appear (call this (eg): RowVariable). Then do a simple IF statement:

IF: Original7Variable.ToString=RowVariable.ToString.
In the THEN column, put a message box like “This is a duplicate”
in the ELSE column, make a add data row activity to add all the other row info you need, then append range to new data table.

I know when you first start RPA it’s really confusing, so if this sounds like i’m speaking alien language, hit me up and i will see what i can sort for you. Just provide where you’re struggling and we can go from there.

Cheers