Is there a better way to test a column for non-sequential numbers?

[JenniferMurphy]

I need to check if a table column contains only the counting numbers (1, 2, 3, 4, ...) and blanks.

I found the KuTools add-in that claims to be able to do this. Does anyone have experience with this add-in? It says it contains over 300 other tools. I prefer to limit add-ins as much as possible to keep Excel clean and simple.

I then created this sheet which I thought did the job with a couple of helper cells and the duplicate values test. Here it is with four test columns. The Good column has correct values. The Dup column has duplicate values which the test finds and the duplicate values test highlights. The Gap column has a missing number, which the test detects, but it is not highlighted. The Oops column shows a failure of the test where there are two errors that add up to the correct sum. (sigh)

Sequential Numbers.xlsx
	C	D	E	F
4	Good	Dup	Gap	Oops
5	5	5	5	5
6
7	2	2	2	3
8	4	4	4	3
9
10	1	1	1	1
11	6	5	7	6
12
13	3	3	3	3
14
15	21	20	22	21
16	6	5	7	6
17	6	6	6	6
18	OK	Error	Error	OK
Test

Cell Formulas
Range	Formula
C15	C15	=SUBTOTAL(109,[Good])
D15	D15	=SUBTOTAL(109,[Dup])
E15	E15	=SUBTOTAL(109,[Gap])
F15	F15	=SUBTOTAL(109,[Oops])
C16	C16	=MAX(TblSeq[Good])
D16	D16	=MAX(TblSeq[Dup])
E16	E16	=MAX(TblSeq[Gap])
F16	F16	=MAX(TblSeq[Oops])
C17	C17	=COUNT(TblSeq[Good])
D17	D17	=COUNT(TblSeq[Dup])
E17	E17	=COUNT(TblSeq[Gap])
F17	F17	=COUNT(TblSeq[Oops])
C18	C18	=IF(AND(C16=C17,TblSeq[[#Totals],[Good]]=(C16*(C16+1)/2)),"OK","Error")
D18	D18	=IF(AND(D16=D17,TblSeq[[#Totals],[Dup]]=(D16*(D16+1)/2)),"OK","Error")
E18	E18	=IF(AND(E16=E17,TblSeq[[#Totals],[Gap]]=(E16*(E16+1)/2)),"OK","Error")
F18	F18	=IF(AND(F16=F17,TblSeq[[#Totals],[Oops]]=(F16*(F16+1)/2)),"OK","Error")

I could write a VBA macro to load the list, sort it, and check it. I could then highlight the problem cells. But then I'd have to remember to run it.

Any comments?

 

[Joe4]

For your row 18, I think the following formula should accurately identify which ones are good.
Place this formula in cell C18 and copy to D18:F18:

=LET(c,SUM(IF(FREQUENCY(C5:C14,C5:C14)>0,1)),IF(AND((MAX(C5:C14)-MIN(C5:C14)+1)=c,COUNT(C5:C14)=c),"OK","Error"))

Highlighting the "bad values" may prove to be a bit more tricky.
- How are you currently highlighting the duplicates now (are you using Conditional Formatting? if so, how exactly do you have it set up)?
- Should every column counter start with number 1?
- If not, how do we know which value should be highlighted?
For example, in your column E example, how do we know if the 1 or the 7 is the "bad" value if we do not know exactly what number our counter should start at?

 

[JenniferMurphy]

For your row 18, I think the following formula should accurately identify which ones are good.
Place this formula in cell C18 and copy to D18:F18:

=LET(c,SUM(IF(FREQUENCY(C5:C14,C5:C14)>0,1)),IF(AND((MAX(C5:C14)-MIN(C5:C14)+1)=c,COUNT(C5:C14)=c),"OK","Error"))

Wow! That will take me a few minutes and a nap to understand. Thanks.

 

[nemmi69]

to get a count of all . Once typed use Ctrl-Shift_Enter

 

[Georgiboy]

Another option:

Book1
	A	B	C	D
1	Good	Dup	Gap	Oops
2	5	5	5	5
3
4	2	2	2	3
5	4	4	4	3
6
7	1	1	1	1
8	6	5	7	6
9
10	3	3	3	3
11
12	OK	Error	Error	Error
Sheet1

Cell Formulas
Range	Formula
A12	A12	=LET(r,TblSeq[Good], f,SORT(FILTER(r,r<>"")), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(SUM(IF(f=sq,1,0))=ROWS(f),"OK","Error"))
B12	B12	=LET(r,TblSeq[Dup], f,SORT(FILTER(r,r<>"")), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(SUM(IF(f=sq,1,0))=ROWS(f),"OK","Error"))
C12	C12	=LET(r,TblSeq[Gap], f,SORT(FILTER(r,r<>"")), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(SUM(IF(f=sq,1,0))=ROWS(f),"OK","Error"))
D12	D12	=LET(r,TblSeq[Oops], f,SORT(FILTER(r,r<>"")), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(SUM(IF(f=sq,1,0))=ROWS(f),"OK","Error"))

 

[JenniferMurphy]

Another option:

Another wow! Now I'll need two naps.

Thanks

 

[nemmi69]

We aim to please

 

[Georgiboy]

As for highlighting the issue cells, best i could come up with below:

Book1
	A	B	C	D
1	Good	Dup	Gap	Oops
2	5	5	5	5
3
4	2	2	2	3
5	4	4	4	3
6
7	1	1	1	1
8	6	5	7	6
9
10	3	3	3	3
11
12
13				3
14
15				3
16
17		5	7
Sheet1

Cell Formulas
Range	Formula
A12:A17	A12	=LET(r,TblSeq[Good], f,SORT(FILTER(r,r>0)), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(f<>sq,f,""))
B12:B17	B12	=LET(r,TblSeq[Dup], f,SORT(FILTER(r,r>0)), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(f<>sq,f,""))
C12:C17	C12	=LET(r,TblSeq[Gap], f,SORT(FILTER(r,r>0)), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(f<>sq,f,""))
D12:D17	D12	=LET(r,TblSeq[Oops], f,SORT(FILTER(r,r>0)), sq,SEQUENCE(ROWS(f),1,MIN(f),1), IF(f<>sq,f,""))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D10	Expression	=IF(COUNTIF(A$12:A$17,A2)>0,TRUE,FALSE)	text	NO

 

[Georgiboy]

Tell a lie, i will leave it at the below:

Book1
	A	B	C	D
1	Good	Dup	Gap	Oops
2	5	5	5	5
3
4	2	2	2	3
5	4	4	4	3
6
7	1	1	1	1
8	6	5	7	6
9
10	3	3	3	3
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D10	Expression	=ISNUMBER(LET(r,A$2:A$10, f,SORT(FILTER(r,r<>"")), sq,SEQUENCE(ROWS(f),1,MIN(f),1), res,UNIQUE(IF(f<>sq,f,"")),MATCH(A2,FILTER(res,res<>""),0)))	text	NO

 

[JenniferMurphy]

As for highlighting the issue cells, best i could come up with below:

Yikes! I can see that you do not want me to get any sleep.