formula for missing numbers in a range based on criteria

[mq1973]

Hi everyone,

I am looking for a formula that finds missing numbers in a range based on criteria. I searched but there was nothing based on criteria.

Here's an example of my data set.

table.jpg

In this example, I need to know which number(s) are missing for report 20-A-1, version 0. The answer should be 2, of course. Similarly, I need to know which number(s) are missing for report 20-A-1, version 1. The answer should be 2, 3, and 4

The last report, 21-B-2, version 0 doesn't have any missing numbers, so the formula should not return anything.

The sample file is attached.

Thank you for your help.

[Bo_Ry]

Please try

=MID(SUBSTITUTE(TEXT(NPV(9,IF(ISNA(MATCH(ROW(INDEX(Z:Z,MIN(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10))):INDEX(Z:Z,MAX(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10)))),IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10),)),ROW(INDEX(Z:Z,MIN(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10))):INDEX(Z:Z,MAX(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10))))*10^9)),REPT("\,0",9)),",0",),2,20)  

Ctrl+Shift+Enter

[kvsrinivasamurthy]

Try this.
In G2 then copied across.

Please Login or Register  to view this content.

[mq1973]

Please try

=MID(SUBSTITUTE(TEXT(NPV(9,IF(ISNA(MATCH(ROW(INDEX(Z:Z,MIN(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10))):INDEX(Z:Z,MAX(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10)))),IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10),)),ROW(INDEX(Z:Z,MIN(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10))):INDEX(Z:Z,MAX(IF(($A$2:$A$10=A2)*($B$2:$B$10=B2),$C$2:$C$10))))*10^9)),REPT("\,0",9)),",0",),2,20)  

Ctrl+Shift+Enter

Thank you so much Bo-Ry. This solution is perfect. I love that the missing numbers are in one cell. Thank you so much

[mq1973]

Try this.
In G2 then copied across.

Please Login or Register  to view this content.

Thanks you so much kvsrinivasamurthy for your help. I really appreciate it. While your solution works of course, Bo-Ry solution works better for because I can copy down the formula. I have hundreds of rows of data and ability to copy down the formula makes is easier.

[mq1973]

Hi Bo-Ry,

I was testing the formula and noticed that it didn't work when I changed the item # in cell 10 to 13. The formula shows 2,3,4,5,7,9,1,2 but it should be 2,3,4,5,7,9,10,11,12
Attachment 765593

[JeteMc]

Selecting Attachment 765593 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
Please utilize the information in the banner at the top of the page to attach files.

[mq1973]

Selecting Attachment 765593 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
Please utilize the information in the banner at the top of the page to attach files.

Thanks, JeteMc for letting me know. I attached a new Excel file and an image as well.

missing number.png

[kvsrinivasamurthy]

ARRAY formula in D2 then copy down.

Please Login or Register  to view this content.

[mq1973]

ARRAY formula in D2 then copy down.

Please Login or Register  to view this content.

Perfect! Thank you so much kvsrinivasamurthy! I appreciate your help.

[kvsrinivasamurthy]

If you interested.
This formula gives the missing numbers till next occurrence.
In F2 copy down(ARRAY formula)

Please Login or Register  to view this content.

[mq1973]

If you interested.
This formula gives the missing numbers till next occurrence.
In F2 copy down(ARRAY formula)

Please Login or Register  to view this content.

Thank you again. For my application, your earlier solution works better since I only need to know the missing numbers within a range.

Best regards,
M