• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consecutive Numbers

S

sprlvlyldy

New Member
Ok,

I don't know if there is a way to do this, but I figure if there is, someone here would know! I have a very large excel file, 650388 rows to be exact. Column A contains numbers. Is there a way to check and see or somehow indicate on the spreadsheet where the numbers aren't consecutive, where they skip a few?


Thank you in advance!! =)
 
Hi, sprlvlyldy! (hope I wrote it right)

If your numbers in column A are ordered ascendently, you can select A2 cell, click on Conditional Format, enter formula, and type this:

=Y(ESNUMERO(A1);ESNUMERO(A2);A2<>A1+1) -----> in english: =AND(ISNUMBER(A1),ISNUMBER(A2),A2<>A1+1)

Choose the format you want, apply to range =$A2:$A1048576.

But I should add that if you have more than half a million lines checking which lines are highlighted/coloured or whatsoever you set, it may requires lots of page downs... Even if you filter by color for example, it'll all depend on how many cases you have. Take this just as an alert.

Regards!
 
Hi SirJB7

thanks for your response...I am new to all this neat excel stuff. I go to the conditional formatting in the Styles section under the home tab, correct? If so, where would I enter the formula that you provided?


Thank you again!!
 
Hi, <difficult nickname, my fingers can't find the way out>!

Home tab, Styles group, Conditional Format icon, New Rule, Use a formula... (in my Excel in spanish 'Utilice una fórmula...", last option from first upper half-pane), and in the inbox "Format values where..." ("Dar formato a los valores donde...") type the previous post's formula, then click on Format button, choose desired format, Accept/Ok, check in Preview box, Accept/Ok, back to Conditional Format, Admin Rules, click on the rule to update (first time, the only one there), in the text box Applies To enter the range to which the format should be applied, then Accept/Ok.

That's all.

Regards!
 
You must log in or register to reply here.
Back
Top