Compare Two Cells to Match or display what does not match

gwson

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

Genevieve P.

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

gwson

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

Genevieve P.

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

gwson

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