Forum Discussion
Solved
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 |
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,
- 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
