Home

Sequence Continuity checking

%3CLINGO-SUB%20id%3D%22lingo-sub-473978%22%20slang%3D%22en-US%22%3ESequence%20Continuity%20checking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-473978%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everybody.%20Hopefully%20someone%20will%20provide%20the%20answer%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20in%20column%20A%2C%20starting%20at%20A1%2C%26nbsp%3B%20have%20the%20following%20as%20an%20example%3A%3C%2FP%3E%3CP%3EA%2001%3CBR%20%2F%3EA%2002%3CBR%20%2F%3EA%2003%3CBR%20%2F%3EA%2004%3CBR%20%2F%3EA%2006%3CBR%20%2F%3EA%2007%3CBR%20%2F%3EA%2015%3CBR%20%2F%3EA%2016%3CBR%20%2F%3EA%2017%3CBR%20%2F%3EA%2018%3CBR%20%2F%3EA%2019%3CBR%20%2F%3EA%2019%3CBR%20%2F%3EA%2020%3CBR%20%2F%3EA%2021%3CBR%20%2F%3EA%2029%3CBR%20%2F%3EA%2030%3CBR%20%2F%3EA%2031%3CBR%20%2F%3EA%2033%3CBR%20%2F%3EA%2034%3C%2FP%3E%3CP%3EThe%20number%20of%20rows%20will%20vary.%20The%20column%20will%20always%20be%20sorted%20ascending.%20I%20want%20to%20be%20able%20to%20check%20all%20the%20cells%20for%20sequence%20continuity.%20Whenever%20a%20break%20in%20that%20continuity%20is%20found%20then%20the%20last%20cell%20before%20the%20break%20in%20sequence%20will%20be%20highlighted%20light%20red%20as%20a%20fill%20colour.%20There%20might%20also%20be%20an%20occasional%20duplicate%20value.%20When%20duplicates%20are%20encountered%20the%20second%20occurrence%20should%20be%20ignored%20for%20sequence%20continuity%20checking%20but%20its%20cell%20highlighted%20yellow.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20assistance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-473978%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-474047%22%20slang%3D%22en-US%22%3ERe%3A%20Sequence%20Continuity%20checking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-474047%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323744%22%20target%3D%22_blank%22%3E%40Chalky1955%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20can%20be%20done%20using%20some%20sort%20of%20formulas%20in%20Conditional%20Formatting%20rules.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERule%201%20(duplicate%20value)%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTIF(%24A%241%3AA1%2CA1)%26gt%3B1%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERule%202%20(sequence%20continuity%20checking)%3A%3C%2FP%3E%3CPRE%3E%3DAND(ABS(A2-A1)%26gt%3B1%2CABS(A2-A1)%26lt%3B%26gt%3BA1)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109580i7613BF4A1633FDE5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Sequence%20continuity%20checking.png%22%20title%3D%22Sequence%20continuity%20checking.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chalky1955
Occasional Visitor

Hi everybody. Hopefully someone will provide the answer:

 

Cells in column A, starting at A1,  have the following as an example:

A 01
A 02
A 03
A 04
A 06
A 07
A 15
A 16
A 17
A 18
A 19
A 19
A 20
A 21
A 29
A 30
A 31
A 33
A 34

The number of rows will vary. The column will always be sorted ascending. I want to be able to check all the cells for sequence continuity. Whenever a break in that continuity is found then the last cell before the break in sequence will be highlighted light red as a fill colour. There might also be an occasional duplicate value. When duplicates are encountered the second occurrence should be ignored for sequence continuity checking but its cell highlighted yellow. 

 

Thanks for any assistance

 

1 Reply

Hi@Chalky1955,

 

This is can be done using some sort of formulas in Conditional Formatting rules.

 

Rule 1 (duplicate value):

=COUNTIF($A$1:A1,A1)>1

 

Rule 2 (sequence continuity checking):

=AND(ABS(A2-A1)>1,ABS(A2-A1)<>A1)

Sequence continuity checking.png

 

 

Hope that helps

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies