Need help matching items in a column

[unpuertomex]

Hello everyone, while I did a search I didn't find anything I can say meets my need which is as follows: I have a serial number that can show up one or multiple times in a column and would like to have the spreadsheet point out if there isn't a match that should be there. For example, in column A2 my serial number is 123456/1/2, the slashed represent the number of time it should appear on the spreadsheet( 1 of 2 and 2 of 2), 123456/1/2 and 123456/2/2 would be a match since the two serial numbers are on the spreadsheet, if however, I have 13456/1/3 and 13456/3/3 but 13456/2/3 is missing, the spreadsheet would say there isn't a match since the third serial number (2 of 3) is missing, this could be indicated by stating no match in column B2 until 13456/2/3 is added to the spreadsheet.

Example scenarios list below


Serial No____________ Status
123456789/1/2_______Match
13456/1/3 _________ _No Match
13456/3/3 _______ ___No Match
9517452/1/1_________Match
123456789/2/2_______Match


Appreciate the help! Thanks in advance!!

[FlameRetired]

Here's one way.

	A	B	C
1	Serial No
2	123456789/1/2	Match	In B2: =IF(COUNTIF($A$2:$A$6,MID($A2,1,FIND("/",$A2)-1)&"*")=--TRIM(RIGHT(SUBSTITUTE($A2,"/",REPT(" ",20),2),20)),"Match","No Match")
3	13456/1/3	No Match
4	13456/3/3	No Match
5	9517452/1/1	Match
6	123456789/2/2	Match

[unpuertomex]

Worked perfectly, but would you mind explaining what the formula is doing, this would allow me to learn.

Thanks!

[FlameRetired]

I will do my best.

B2 the active cell see the formula in the formula bar. We will work with that.

Select/highlight this portion of that formula in the formula bar. MID($A2,1,FIND("/",$A2)-1)&"*". Hit the F9 function key and you will see this in the formula bar. "123456789*". That part of the formula uses the MID function to isolate that part of the string in A2 preceding the first "/" character --- the serial number. It then appends a "*" (a wildcard) to that number. COUNTIF will accept that wildcard and return a count of items in A2:A6 containing that serial number followed by anything. In this case it is 2.

Now select/highlight TRIM(RIGHT(SUBSTITUTE($A2,"/",REPT(" ",20),2),20)). (that part returns all the characters following the last "/" character in the string.) Hit the F9 function key. That reveals a text "number" --- "2" in this case. "2" has a numeric value of zero. If you include the leading "--" when you hit F9 you will see the number 2. When "--" (called a double unary) or any math operation is applied to text "numbers" it coerces those into their underlying numeric value.

IF confirms or rules out whether he count of serial numbers in A2:A6 equals the expected count and returns a "Match" if it does and "No Match" if it doesn't.

Using the F9 function key in this manner is helpful for analyzing, trouble shooting and self instruction. Beware though that this approach examines the formula out of context and so can be misleading. There is another method.

Excel has a feature called 'Evaluate formula'. (Fx for short). You will find it in the Formulas ribbon. Clicking the Evaluate button repeatedly reveals step by step how Excel calculates the formula. This method reveals the steps in context and is a more reliable way to analyze. The view port is rather tiny though and can make it difficult to use on longer formulas --- especially those referencing large ranges or returning large arrays.

Did this help?