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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,111
Messages
5,857,448
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top