Check If One Column Value Exists in Another Column

11 views
Skip to first unread message

George Milten

unread,
Nov 22, 2021, 7:47:22 AM11/22/21
to openr...@googlegroups.com
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

unread,
Nov 23, 2021, 4:55:30 AM11/23/21
to OpenRefine
Hi George,


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
Reply all
Reply to author
Forward
0 new messages