Break in Sequence

BarbK

New Member
Joined
Jan 21, 2010
Messages
21
Greetings & Happy Friday!
I have a list that I need to identify where there are breaks in the sequence. Would like the result to look like this (the *** indicates a break in the sequence):

<TABLE style="WIDTH: 228pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=304 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6257" width=176><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" width=64 height=19> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 132pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=176>A</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>B</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>1</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00003 0171</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> *** </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>2</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00003 0181</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>3</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00003 0182</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>4</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00003 0183</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> *** </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00005 0181</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>6</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00005 0182</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> *** </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>7</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCDE-36-X-00005 0190</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

I'm using XL2007
Thanks much!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

BarbK

New Member
Joined
Jan 21, 2010
Messages
21
Thanks so much for your response. The formula works great until I change it from 4 to 6 characters, then I get a #VALUE! error. I am downloading the data from our mainframe and it's a customer's order #. I need to analyze the last 6 digits of this field. There will always be a space at digit 5.

Do you have another idea?
 

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
Try This -

=IF(A2="","",IF(((MID(A1,16,1)&RIGHT(A1,4))*1)+1<>(MID(A2,16,1)&RIGHT(A2,4))*1,"***",""))

Hope this helps !
 

BarbK

New Member
Joined
Jan 21, 2010
Messages
21

ADVERTISEMENT

Now I'm getting *** in every cell, not just when the sequence breaks.
I'm a bit new to Excel formulas - would you explain the first part of the formula =IF(A2="",""
 

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
The formula that I gave is to be pasted in cell A1. I hope you are doing that only. If you are pasting the formula in A2, then there will be a change in it. The updated one is given below -

=IF(A3="","",IF(((MID(A2,16,1)&RIGHT(A2,4))*1)+1<>(MID(A3,16,1)&RIGHT(A3,4))*1,"***",""))

This formula needs to be copied through your range.

Coming to your question regarding the first part, wiz 'IF(A3="","" ' , it's used to eliminate any error. so e.g. the values are entered till A10, when you paste this formula in A10, it will compare the details in A10 with A11. Since A11 is blank, it will post an error. So to eliminate this, you need to first check if at all there is anything in next cell or not.

Hope this clarifies. Lemme know if you still face any challenge...
 

BarbK

New Member
Joined
Jan 21, 2010
Messages
21
Thank you for your explanation and patience. I made an error in the cell references and once I corrected them, the formula works perfectly!!! I greatly appreciate your help.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,637
Messages
5,512,544
Members
408,903
Latest member
reehan123

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top