Compare Two Cells to Match or display what does not match
Hello I need to compare two cells and display result in a third cell. I need to know if numbers from column B cell match against numbers included in column A cell . Column A cell may have more numbers than column B cell. If one or more numbers from column B cell do not exist in Column A cell, I will need to display which ones are not included in column C cell. If numbers from column B cell all match with column A cell, I need to display Yes.
Column A cell contains the following course registration numbers from an output of a join/collect function
CRN: 12345  Art
CRN: 53245  Nursing
CRN: 67545  Bio
CRN: 76234  History
CRN: 34853  The
Column B cell contains just the course registration numbers from a join/collect function:
12345
73495
23453
76234
Best Answers

Hi @gwson
There's a way you can check if the exact numbers are contained in Column A, however I'm not sure it's possible to then filter out the matching values and only show the numbers that are missing.
The way I would personally do this is to add a helper column that extracts only the number from your Column A data. This is going to look like quite a long formula, but I'm essentially replacing every possible text value (each letter of the alphabet) with "" or blank, so we end up with only the number:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Column A]@row, "CRN: ", ""), "A", ""), "B", ""), "C", ""), "D", ""), "E", ""), "F", ""), "G", ""), "H", ""), "I", ""), "J", ""), "K", ""), "L", ""), "M", ""), "N", ""), "O", ""), "P", ""), "Q", ""), "R", ""), "S", ""), "T", ""), "U", ""), "V", ""), "W", ""), "X", ""), "Y", ""), "Z", ""), "  ", ""), "a", ""), "b", ""), "c", ""), "d", ""), "e", ""), "f", ""), "g", ""), "h", ""), "i", ""), "j", ""), "k", ""), "l", ""), "m", ""), "n", ""), "o", ""), "p", ""), "q", ""), "r", ""), "s", ""), "t", ""), "u", ""), "v", ""), "w", ""), "x", ""), "y", ""), "z", "")
Once you have this data, you can compare it to your Column B to see if it's an exact match. This would be a simple IF statement:
=IF([Helper 1]@row = [Column B]@row, "Yes", "Not a match")
You could of course set this up to return a symbol instead of text (such as a flag, if it isn't a match).
I hope this helps!
Cheers,
Genevieve

Wow this is very good, and it works well. However we ended up with the need to add some extra numbers in Column A, is it possible to strip out those extra numbers or strip out everything but the CRN number itself: "12345"
Here is the update Column A value
CRN: 12345  ART: 3119  VA
Answers

Hi @gwson
There's a way you can check if the exact numbers are contained in Column A, however I'm not sure it's possible to then filter out the matching values and only show the numbers that are missing.
The way I would personally do this is to add a helper column that extracts only the number from your Column A data. This is going to look like quite a long formula, but I'm essentially replacing every possible text value (each letter of the alphabet) with "" or blank, so we end up with only the number:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Column A]@row, "CRN: ", ""), "A", ""), "B", ""), "C", ""), "D", ""), "E", ""), "F", ""), "G", ""), "H", ""), "I", ""), "J", ""), "K", ""), "L", ""), "M", ""), "N", ""), "O", ""), "P", ""), "Q", ""), "R", ""), "S", ""), "T", ""), "U", ""), "V", ""), "W", ""), "X", ""), "Y", ""), "Z", ""), "  ", ""), "a", ""), "b", ""), "c", ""), "d", ""), "e", ""), "f", ""), "g", ""), "h", ""), "i", ""), "j", ""), "k", ""), "l", ""), "m", ""), "n", ""), "o", ""), "p", ""), "q", ""), "r", ""), "s", ""), "t", ""), "u", ""), "v", ""), "w", ""), "x", ""), "y", ""), "z", "")
Once you have this data, you can compare it to your Column B to see if it's an exact match. This would be a simple IF statement:
=IF([Helper 1]@row = [Column B]@row, "Yes", "Not a match")
You could of course set this up to return a symbol instead of text (such as a flag, if it isn't a match).
I hope this helps!
Cheers,
Genevieve

Wow this is very good, and it works well. However we ended up with the need to add some extra numbers in Column A, is it possible to strip out those extra numbers or strip out everything but the CRN number itself: "12345"
Here is the update Column A value
CRN: 12345  ART: 3119  VA
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!