simple: Identify a cell that is out of number sequence in a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Excel
How do I ID the specific cell that has fallen out of sequence in a list?
Example:

A1= 1
A2= 2
A3= 3
A4= 5 (flag this cell because the number "4" is missing)
A5= 6

Do I use a formula or is there a menu option I can use?
 
Try this - select A2 thru the end of your list. (Don't include the first
item, impossible for it to be out of sequence...)
Choose, Format, Conditional Formatting, change Cell value is... to Formula
is...
and enter the following:
=A2<>A1+1
(Exactly as above - be sure it's not =$A$2<>$A$1+1)
click the format button, the patterns tab, and choose a color...
click ok to see A4 in your example flagged.
 
Thank you so much for replying to my post. I tried this on my example and it
worked! the cells on my actual database have a mix of letters and number,
with which it didn't work. Does this factor change things? the actual
contents of my cells are:

A1= 07SA100001
A2= 07SA100002
A3= 07SA100003
A4= 07SA100005 (flag this cell)
A5= 07SA100006

Do I need to format the cells a certain way, like as a number?
 
Formatting makes no difference, besides you cannot format text which is what
you got.
You would need to break out the numbers and assuming that they always start
with 07SA
you can use

=--MID(A1,5,255)

copy down the do whatever you did that worked on this column instead


--


Regards,


Peo Sjoblom
 
Peo Sjoblom,

What do you mean by "break out the numbers?" and what does your last
sentence mean?
 
Peo Sjoblom,

What do you mean by "break out the numbers?" and what does your last
sentence mean?

I added that formula to the "conditional formattiong" and it flagged all my
selected cells.
 
Break out the numbers mean just what it says, to Excel anything with alpha
characters will always be seen as text thus it won't work without separating
the number sequence you want to test as a number. The last sentence means
that after you have parsed out the numbers in a help column use the formula
you said worked using CF when you tested it on your example. Btw if you want
an answer that will work you should post an example as close as possible to
the data you really want to use the solution on. It's a big difference
between

1
2
3

and

07SA100001
07SA100002
07SA100003




--


Regards,


Peo Sjoblom
 
07SA100001
07SA100002
07SA100003
07SA100005
07SA100006

this IS the actual data that is in my database which is why I included it.
So you are saying that I need to take the letters out of all the cells I want
to test?
 
Hi

What Peo is saying, is that you need to use an extra column, which will be
used as the criteria cells for applying the Conditional formatting to your
main list of cells.

Assuming your list is in column A, and that the first non used column on
your sheet is column F.
In cell F1, use Peo's formula =--MID(A1,5,255)
Copy this down the column as far as required.

Then apply Conditional formatting to column A, but amend BoniM's formula to
=F2<>F1+1
 
Try this:

B1: (leave it blank)
B2: =IF(MID(A1,5,6)+1=MID(A2,5,6)+0,"","07SA"&MID(A1,5,6)+1&" is missing")
 
To make it work for your actual data, do everything else the same, but use
this formula instead:
=VALUE(RIGHT(A2,5))<>VALUE(RIGHT(A1,5))+1
 
Back
Top