Find Missing Sequence

[FlashNZ]

Hi,

I want to identify missing numbers in a sequence of numbers but with conditions. I can't figure out to write a IF statement to do this (or maybe it needs a Macro).

I have highlighted the sequence I am trying to identify in the image in the column FY. But I only want it to identify this break in the sequence if the ItemID is the same and if the MSQ is greater than 0. So I guess I want:- IF MSQ > 0 AND ItemID = the ItemID above it then evaluate the sequence and report "Missing" in a new column E, otherwise leave it blank.

Hope this makes sense.

Gordon

 

[Peter_SSs]

For next time this might be useful so helpers do have to manually type sample data. It will usually get you faster & more accurate answers.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Would something like this help?

21 10 21.xlsm
	A	B	C	D
1
2	a	2020	1
3	a	2020	1
4	a	2020	1
5	a	2020	1
6	a	2022	1	x
7	a	2022	1
8	a	2022	1
9	a	2022	1
10	b	2020	0
11	b	2020	0
12	b	2022	0
13	b	2022	0
14	b	2022	0
Seq Break

Cell Formulas
Range	Formula
D2:D14	D2	=IF(AND(A2=A1,C2>0,B2<>B1),"x","")

 

[FlashNZ]

HI,

Thanks for the quick reply and have now downloaded the add in and posted table below.

I don't think I explained myself properly at the start, yes your formula identifies the highlighted break in the sequence for NUT-FENOIL but the gap I want to identify is that 2021 is missing (goes from 2020 to 2022). Your formula also identifies the change for NUT-FENUGR from 2015 to 2016. I only want it identified if there is a gap so if NUT-FENGUR went from 2015 to 2017 I would want it identified. Changing from 2015 to 2016 is ok.

Does that explain it?

Book1.xlsx
	A	B	C	D	E
1	Item Number	FY	Period No:	MSQ	Missing Sequnce
2	NUT-FENOIL	2020	8	390
3	NUT-FENOIL	2020	9	390
4	NUT-FENOIL	2020	10	390
5	NUT-FENOIL	2020	11	390
6	NUT-FENOIL	2020	12	390
7	NUT-FENOIL	2022	1	390	x
8	NUT-FENOIL	2022	2	390
9	NUT-FENOIL	2022	3	390
10	NUT-FENOIL	2022	4	390
11	NUT-FENOIL	2022	5	380
12	NUT-FENOIL	2022	6	380
13	NUT-FENOIL	2022	7	380
14	NUT-FENOIL	2022	8	380
15	NUT-FENOIL	2022	9	380
16	NUT-FENOIL	2022	10	380
17	NUT-FENOIL	2022	11	380
18	NUT-FENOIL	2022	12	380
19	NUT-FENUGR	2015	1	0
20	NUT-FENUGR	2015	2	0
21	NUT-FENUGR	2015	3	0
22	NUT-FENUGR	2015	4	0
23	NUT-FENUGR	2015	5	0
24	NUT-FENUGR	2015	6	0
25	NUT-FENUGR	2015	7	0
26	NUT-FENUGR	2015	8	0
27	NUT-FENUGR	2015	9	0
28	NUT-FENUGR	2015	10	0
29	NUT-FENUGR	2015	11	0
30	NUT-FENUGR	2015	12	0
31	NUT-FENUGR	2016	1	0	x
32	NUT-FENUGR	2016	2	0
33	NUT-FENUGR	2016	3	0
34	NUT-FENUGR	2016	4	0
35	NUT-FENUGR	2016	5	0
Sheet1

Cell Formulas
Range	Formula
E2:E35	E2	=IF(AND(A2=A1,C2>0,B2<>B1),"x","")

 

[Peter_SSs]

OK, try adding one extra condition. Does this do it?

=IF(AND(A2=A1,C2>0,B2<>B1,B2<>N(B1)+1),"x","")

 

[FlashNZ]

Hi,

I figured it out: =IF(AND(A2=A1,D2>0,(B2-B1)>1),"x","")

Thanks for your help.

Gordon

 

[Peter_SSs]

You're welcome. Glad you got it sorted.