SOLVED

Missing numbers in a column of repeating numbers

Copper Contributor

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. 

Column1Column2Column to find missing number
XYZ202
XYZ513
XYZ806
XYZ1027
XYZ1109
XYZ12110
XYZ14113
XYZ21018
ABC26622
ABC27824
ABC28025
ABC29026
ABC31028
ABC35032
ABC36133
NOP624
NOP705
NOP806
NOP1027
NOP1109
NOP12110
NOP13111
NOP14012
NOP14113
11 Replies
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
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?
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

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

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

davidleal_0-1680027769111.png

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,

 

Thank you! This helps so much!! It worked like a charm.
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
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

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

 

A1 Original Query Result
B1 A3,5
C3 B2,5
A2 C2,4
B3   
C1   
A4 New Result
B4 3A
C5 2B, C
   4C
   5A,B
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.
Thank you so much! You have save me so much manual verification! I really need to learn more formulas in Excel
1 best response

Accepted Solutions
best response confirmed by Katescott (Copper Contributor)
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:

davidleal_0-1680027769111.png

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,

 

View solution in original post