 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2007simple: Identify a cell that is out of number sequence in a list
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. > In Excel > How do I ID the specific cell that has fallen out of sequence in a list? [quoted text clipped - 7 lines] > > Do I use a formula or is there a menu option I can use? 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? > Try this - select A2 thru the end of your list. (Don't include the first > item, impossible for it to be out of sequence...) [quoted text clipped - 17 lines] > > > > Do I use a formula or is there a menu option I can use? 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  SignatureRegards, Peo Sjoblom > Thank you so much for replying to my post. I tried this on my example and > it [quoted text clipped - 33 lines] >> > >> > Do I use a formula or is there a menu option I can use? Peo Sjoblom, What do you mean by "break out the numbers?" and what does your last sentence mean? > Formatting makes no difference, besides you cannot format text which is what > you got. [quoted text clipped - 43 lines] > >> > > >> > Do I use a formula or is there a menu option I can use? 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. > Formatting makes no difference, besides you cannot format text which is what > you got. [quoted text clipped - 43 lines] > >> > > >> > Do I use a formula or is there a menu option I can use? 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 SignatureRegards, Peo Sjoblom > Peo Sjoblom, > [quoted text clipped - 57 lines] >> >> > >> >> > Do I use a formula or is there a menu option I can use? 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? > 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 [quoted text clipped - 76 lines] > >> >> > > >> >> > Do I use a formula or is there a menu option I can use? 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 SignatureRegards Roger Govier > 07SA100001 > 07SA100002 [quoted text clipped - 97 lines] >> >> >> > >> >> >> > Do I use a formula or is there a menu option I can use? 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") > 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, [quoted text clipped - 30 lines] > > > > > > Do I use a formula or is there a menu option I can use? 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 > 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, [quoted text clipped - 30 lines] > > > > > > Do I use a formula or is there a menu option I can use? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.