Check If One Column Value Exists in Another Column

[George Milten]

Hello all,

how could i achieve the following in openrefine?

https://www.got-it.ai/solutions/excel-chat/excel-tutorial/vlookup/check-if-one-column-value-exists-in-another-column

Thank you in advance for your kind help

[Owen Stephens]

Hi George,

You can use the 'cross' function for this. See https://docs.openrefine.org/next/manual/grelfunctions#crosscell-s-projectname-optional-s-columnname-optional for the documentation

Cross is usually used for looking up data from one project in another project, but it also works within a project to look up a value from one cell in a different column in the same project. So for example if we have "Project 1" with "Column A" and "Column B". We want to know if a value in a cell in Column A appears somewhere in Column B(not necessarily the same row, just anywhere in the Column). To achieve this you can add a "Custom text facet" in Column A with the expression:

value.cross("Project 1", "Column B").length()

This will give you a facet with the number of matches the value in Column A has in Column B. Those that give an error have no matches. You can further change the expression used if you want different information in the facet.

Hope this helps

Owen