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?

 

[JenniferMurphy]

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

Your lies are worth more than my truths. Too bad you are in England. I was going to suggest that you run for political office. We seem to have a dearth of truth there.

 

[Peter_SSs]

Possibly this?

23 05 04.xlsm
	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	TRUE	FALSE	FALSE	FALSE
Check Sequence

Cell Formulas
Range	Formula
A12	A12	=LET(r,TblSeq[Good],s,SORT(FILTER(r,r<>"")),AND(s=SEQUENCE(ROWS(s))))
B12	B12	=LET(r,TblSeq[Dup],s,SORT(FILTER(r,r<>"")),AND(s=SEQUENCE(ROWS(s))))
C12	C12	=LET(r,TblSeq[Gap],s,SORT(FILTER(r,r<>"")),AND(s=SEQUENCE(ROWS(s))))
D12	D12	=LET(r,TblSeq[Oops],s,SORT(FILTER(r,r<>"")),AND(s=SEQUENCE(ROWS(s))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D10	Expression	=AND(A2<>"",OR(COUNTIF(A$2:A$10,A2)>1,ISNA(MATCH(A2,SEQUENCE(COUNT(A$2:A$10)),0))))	text	NO

 

[Joe4]

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

To break it down, I am really just doing three separate calculations, and then checking to see if the value if each is exactly the same.

This part calculates the number of unique entries (ignoring blanks):

SUM(IF(FREQUENCY(C5:C14,C5:C14)>0,1))

This part calculates the range (span) between the lowest and highest values:

MAX(C5:C14)-MIN(C5:C14)+1)

And then this part counts the total number of non-blank entries:

COUNT(C5:C14)

Then I am just using an AND function to check to see if all 3 values are equal to each other.
I am using a LET to simplify things, so I don't need to calculate the first value twice.

If all three are equal, then we know we have consecutive entries with no duplicates (as long as we are only dealing with whole numbers).

 

[JenniferMurphy]

To break it down, I am really just doing three separate calculations, and then checking to see if the value if each is exactly the same.

That's very helpful. I let myself get freaked out over the use of the Let function in most of the examples. After your breakdown, I broke down and checked it out. Very simple and very handy. One of the reasons I had eschewed in-cell formulas ad opted for VBA is the need to repeat complex expressions, which leads to errors. The Let function makes thay much easier.

Thank you

 

[JenniferMurphy]

Possibly this?

That looks like a nice compact solution. Let me take a quick nap and then I'll dig into it.

Thanks