Excel Expert - Help!

[LisaContractorUmbrella]

Hi guys

Can any of you help me - I need to format an Excel list so that it will highlight non-sequential numbers - any ideas????

[Spacecadet]

Hi guys

Can any of you help me - I need to format an Excel list so that it will highlight non-sequential numbers - any ideas????

define non sequential?

1
2
3
5
6
7
8

1
2
3
7
4
5
6

[Spacecadet]

and which version of excel are you using?

[LisaContractorUmbrella]

Thanks SpaceCadet - it would be option 2 but would not run 1 to 10 ie:

125249
125250
125252
125254
125255 etc

Am using Microsoft 2007

[northernladuk]

Like this???

Flagging non seq numbers

[Moscow Mule]

Conditional formatting.

• Select the second cell in your range, then conditional formatting.
• Then, change to "Formula is"
• assuming the second cell in your range is D6, your formula is
  • =IF($D6-$D5<>1,1,0) (assumes the sequence adds 1 each time)
• set the format to red text (or whatever).
• Then use the format painter to populate to the rest of your range.

[Spacecadet]

=IF($D6-$D5<>1,1,0) (assumes the sequence adds 1 each time)

You can just use this in 2007:

=$D6-$D5<>1

[BoredBloke]

Thanks SpaceCadet - it would be option 2 but would not run 1 to 10 ie:

125249
125250
125252
125254
125255 etc

Am using Microsoft 2007

Is this a one off or is it something you's dave to do on a regular basis.

If it was a one off I'd simply take the lower number away from the higher one in the cell to the right and if the value was anything other than 1 I'd investivate these.

If it is a regular thing then use conditional formatting

If your data is in b4 onwards, highlight cell B4, and apply conditional formatting - with 'formula is' and the formula =$B4-$B3<>1

use the format button to change the formatting and ok it. Then copy the formats down your range. This will highlight all the non sequential elements and the last element (as it would be comparing it to the blank cell at tyhe end!)

[northernladuk]

Does LIsa know how much contractors charge per post for consultancy???

[LisaContractorUmbrella]

Sorted
Conditional formatting worked a treat

Thanks for all your help guys - much appreciated