Mar 27 2023 01:36 PM - edited Mar 27 2023 01:40 PM
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 |
Mar 27 2023 01:48 PM
Mar 27 2023 02:07 PM
Mar 28 2023 09:07 AM
Mar 28 2023 10:11 AM - edited Mar 28 2023 10:14 AM
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?
Mar 28 2023 11:23 AM - edited Mar 31 2023 05:31 PM
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,
Mar 30 2023 07:47 AM
Mar 30 2023 08:22 AM
Mar 30 2023 09:20 AM
Mar 31 2023 01:35 PM
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 |
Mar 31 2023 05:28 PM
Apr 04 2023 09:14 AM
Mar 28 2023 11:23 AM - edited Mar 31 2023 05:31 PM
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,