SOLVED

# Missing numbers in a column of repeating numbers

Copper Contributor

# Missing numbers in a column of repeating numbers

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
11 Replies

# Re: Missing numbers in a column of repeating numbers

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

# Re: Missing numbers in a column of repeating numbers

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?

# Re: Missing numbers in a column of repeating numbers

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

# Re: Missing numbers in a column of repeating numbers

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?

best response confirmed by Katescott (Copper Contributor)
Solution

# Re: Missing numbers in a column of repeating numbers

@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,

# Re: Missing numbers in a column of repeating numbers

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

# Re: Missing numbers in a column of repeating numbers

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

# Re: Missing numbers in a column of repeating numbers

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

# Re: Missing numbers in a column of repeating numbers

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

# Re: Missing numbers in a column of repeating numbers

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.

# Re: Missing numbers in a column of repeating numbers

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