How to tell if a cell value is outside the possible entries


I have an excel sheet and I'm trying to detect whether a column value is contained a pre-defined list or not. This reference list is contained in anoter table column.

I'm using a Rule engine node, but I' having trouble building a rule like

<My value> IN <List of OK values> => "OK"

Is there a way to concatenate the column values into a variable? I might then use this variable in the rule engine...

Thanks in advance


Just thinking from the top of my head, in the second table, could you add an extra column with the entry "Yes" using the Constant Value column node.

Now you could use the Cell Replacer node on the first table, and using the second table as a dictionary table, in which if it finds the entry it adds a Yes. You can choose to append this as an extra column so your original data isn't overwritten.

Hope that is close to what you want.



Hi Angelo,

The concatenate the column values to a single cell can be done using Pivoting or GroupBy (do not group, just aggregate with: Set). After that, you can join that value to the table containing the values to check.

And now you can use the Rule engine node like this:

$Your value$ IN $List of OK values$ => "OK"

Cheers, gabor

PS.: It is too late for me, but the other solution might work too and seems to be less complicated.

Thanks guys, I used Simon's solution and it's exactly what I needed.