Forum Discussion
Solved
Comparing two columns with comma separated string values (match)
Hi,
I currently have two columns of reference data. Each cell contains comma separated values.
My aim is to find a match between any of the patent numbers in column A within the corresponding row in column B. E.g. in row 2, there is a match between value in column 1 (CN...385A) and column 2).
Ideally the output in column C would be "yes" or "match".
Ideally want to keep cells formatted as they are, with delimiters between patent numbers.
many thanks
I hate to be the one to answer my own question, but that is what's happening.
After what I thought was a thorough three day search, I happened across a similar solution used for another like situation.
I found that going into DCOM Config and, in the properties of the Microsoft Excel > Identity tab, change the setting to "The interactive user".
Since this is not a production machine and is being used for testing, I have not looked any deeper than solving my immediate problem. To those who see a security issue, again, this was not a consideration for me in this case. If it was production, I would spend more time considering the collateral of this change.
10 Replies
- Hi,
Just a follow-up question to this.
I was wondering if the output in cell C2 instead of showing Match, could in fact show the matching citation itself.
E.g. in row 2, there is a match between value in column 1 (CN117860385A) and column 2. In column C, the output would be the matching citation: CN117860385A as opposed to "Match".
many many thanksHow about
=LET(a, TEXTSPLIT(A2, "|"), b, TEXTSPLIT(B2, "|"), IFERROR(FILTER(a, ISNUMBER(XMATCH(a, b))), ""))
