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!