I need a formula that is comparing the column “Assignee” and “Inventor”.
If Assignee equals to Inventor nothing should happen. Row0 and Row1 should not be adjusted.
If Assignee is not equal to Inventor like in Row2 and Row3 the certain string value in the column “Assignee” → AUDI should be deleted and replaced by an empty string " ".
Now I realized a more difficult problem in my database:
As you can see in the screenshots, there are the Inventor names in the assignee columns.
But I like to delete all Inventor Names + Country Codes within the Assignee Columns + Country Codes (Here PETER,DE and HANS,DE)
I do not know if I realy understand what you want but I will use more Rule engine nodes, each for one task and append new columns at the end use only new columns …
compare Assignee 1 and Inventor 1 and append the result in new column “AssigneeNew”
$Assignee1$ = $Inventor1$ => $Assignee1$
TRUE => “”
and add new column “AssigneeNew”
compare Assignee 1 and Inventor 1 and append the result in new column “CountryCodeNew”
$Assignee1$ = $Inventor1$ => $Assignee_Country1$
TRUE => “”
and add new column “CountryCodeNew”
compare Assignee 1 and Inventor 1 and append the result in new column “CarBrand”
$Assignee1$ = $Inventor1$ => $Assignee3$
TRUE => “”
and add new column “CarBrand”
You can also check more conditions like
($Assignee1$ = $Inventor1$) AND (Inventor 1 Country = Inventor 2 Country) => $Assignee1$
You can also first concatenate assigne 1 and assigne 2 and then compare them with the concatenated value of Inventor 1 and inventor 2
Indead I only consider the rule for n columns, which means that the rule should be developed for
Assignee 1 = Inventor 1…Asignee(n)=Inventor(n).
That is why the last assignee columns, you can see in the screenshot are mostly companies.
These companies I like to put at the beginning starting from assignee_Harmonized[0]…[n], if inventors are first in place before…
So only the first case you have mentioned should be considered.
Do you know the rule for an unlimited amount of assignees/inventors?
I am thankful for your contribution !
Here is my excel file, than you might imagine it: Example.xlsx (15.4 KB)
have you had any success? If not maybe consider splitting your data on two parts. One with assignee columns and one with inventor columns. This will allow you to use Table Difference Finder node upon which results you can build logic to get what you need.