Missing numbers in a column of repeating numbers

[Katescott]

I am trying to determine missing numbers in a range for multiple values. I have tried multiple ways to drill down to the missing numbers 1-50. The numbers in Column A, B, C are relative, I tried concatenate to create a unique ID but then it isn't telling me the numbers missing in the last column. I am trying to get the missing values I can get the first portion of the sequence to tell me which numbers are missing between 1-50 but I can't get the the rest even when I rewrite the formula. Any help would be appreciated. Below is an example of the data I am working with. 

Column1	Column2	Column to find missing number
XYZ	20	2
XYZ	51	3
XYZ	80	6
XYZ	102	7
XYZ	110	9
XYZ	121	10
XYZ	141	13
XYZ	210	18
ABC	266	22
ABC	278	24
ABC	280	25
ABC	290	26
ABC	310	28
ABC	350	32
ABC	361	33
NOP	62	4
NOP	70	5
NOP	80	6
NOP	102	7
NOP	110	9
NOP	121	10
NOP	131	11
NOP	140	12
NOP	141	13

[#2]

You mentioned that you have numbers in column A, but I don't see them. I am struggling to understand what are the input and the expected output from your sample. Thanks

[#3]

I don't have numbers in column A. Column A is a name, column B is the location, and column C is the conversion 1-50 that matches to Column B. Does that make sense?

[#4]

Understood that in column A there are no numbers. So columns A, and B are input even the column A is not used for finding the missing numbers, correct? then you need to find the missing number from the sequence 1-50 numbers in Column B, so what is Column C, the result? What is the logic of that result? Please spend some time providing more clarity to your problem. We would need to know: the input, expected output, and the logic to obtain the expected output in a clear example. Thanks

[#5]

I am going to try to explain it the best I can.
Column A is unique names:
XYZ = Ford
ABC = Honda
NOP = Jeep
Column B is "color":
10 = Yellow
20 = Pink
51 = Gray
102 = Black
103 = Orange
290 = Green
The numbers originally assigned to the colors are random and have no sequence. So, I have assigned a numbers in sequence (1-50 Column C) to always identify colors ie Yellow = 10 = 1; Pink = 20 = 2; Gray = 51 = 3 etc.
I am trying to find out when Ford (XYZ), Honda (ABC) Jeep (NOP) are missing any number in the 1-50 sequence. Then I can determine from the missing assigned numbers what colors are missing from each name in column A
Does that make sense?

[#6 · Accepted solution]

@Katescott Now it is more clear, you can use the following formula:

=LET(A, A1:A9, B,B1:B9, check,SEQUENCE(3), ux,UNIQUE(A),
 missing,MAP(ux, LAMBDA(x, TEXTJOIN(",",,FILTER(check, 
 ISNA(XMATCH(check,FILTER(B,A=x))),"")))),
 FILTER(HSTACK(ux,missing),missing<>""))

In this sample, we assume the missing values we want to check are in the range of 1-3. Here is the sample output. It returns the rows from the first column with missing values. If more than one is missing, it returns a list separated by a comma. Here is the output, you can adjust to your need in your real situation, adjusting the names: A, B, and check. The last FILTER call is to just show the column row values with missing values only.

Here is the output:

You can also use COUNTIFS as follows:

=LET(A,A1:A9, B,B1:B9, ux,UNIQUE(A), seq,SEQUENCE(,3), 
 cnts,COUNTIFS(A,ux,B,seq), missing,BYROW(cnts,LAMBDA(x,
 TEXTJOIN(",",,FILTER(seq,x=0,"")))), 
 FILTER(HSTACK(ux,missing),missing<>""))

In both cases you can use MAP or BYROW I just changed it for illustrative purposes. For this case, seq has to be a column-wise array.

If you want the result the other way around, i.e. for each number the missing letters, adapting for example the COUNTIFS approach and considering a numeric range or 1-5:

=LET(A,A1:A9, B,B1:B9, ux,UNIQUE(A), seq,SEQUENCE(,5),
 cnts,COUNTIFS(A,ux,B,seq), missing, TOCOL(BYCOL(cnts,LAMBDA(x,
 TEXTJOIN(",",,FILTER(ux,x=0,""))))),
 FILTER(HSTACK(TOCOL(seq),missing),missing<>""))

Now we use BYCOL and transpose the information using TOCOL.

I hope it helps,

[#7]

Thank you! This helps so much!! It worked like a charm.

[#8]

Ok, one more question now that I have all the missing numbers for each, is there a way to take that data and tell me each name that is missing all 1's, 2's etc

XYZ 1,4,5,8,11,12,14,19,23,25,27,33,34,35,40,41,45,48
ABC 1,2,4,6,7,8,14,15,17,19,21,23,27,29,34,35,40,41,45,47,48
NOP 8,27,34,40,45,47

[#9]

Thanks for accepting the answer. About your question, it is not clear to me how you want the result. Please provide a simpler dataset as I did, and the expected result, so I can help you with that. Thanks

[#10]

@davidleal 

Does this make sense? First I needed to determine which numbers were missing, then from that data I need to know all the ones missing the same number. Example below:

A	1		Original Query Result
B	1		A	3,5
C	3		B	2,5
A	2		C	2,4
B	3
C	1
A	4		New Result
B	4		3	A
C	5		2	B, C
			4	C
			5	A,B

[#11]

I see, check my answer with the third formula providing this information, I consider now the number range 1-5. I get the same result as you, but having the missing numbers sorted.

[#12]

Thank you so much! You have save me so much manual verification! I really need to learn more formulas in Excel