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
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!