Alert when consecutive numbers in a list exceed 5

  • Thread starter Thread starter Patricia
  • Start date Start date
P

Patricia

Good morning

I have thousands of records, and need to be alerted when, consecutive
numbers (in a particular column) exceed 5. It has to be consecutive numbers
(not sorted).

eg
data 1
data 1
data 8
data 8
data 8
data 8
data 8
data 8

In this case the number 8 exceeds 5.
Would really appreciate help on this, as at the moment it is really time
consuming.

Thanking you in anticipation.
 
Have you thought about using conditional formatting for this?

Select the cells with the numbers you want to compare.

Format -> Conditional Formatting

Condition 1 -> Formula is
=ABS(B1-B2)>=5

Change the format for the cell so that you can visually find the change.

Alternatively, you could add a column with a calculation of the delta
between the two values and use AutoFilter to find the values that exceed
your limits.
 
Say you have used cols A & B for data and numbers, then in C6 insert:

=IF((B6=B5)+(B6=B4)+(B6=B3)+(B6=B2)+(B6=B1)=5,1,"") amd copy down

If there are 6 consecutive numbers in column B the function will return a 1,
otherwise a blank.
 
Thank you so much for your quick response.

I found that Gary"s Student solution does the job so well - I have used
Barb's idea on the formula cells using conditional formatting to have the
result highlighted.

Thank you both again for your help, really appreciate it.
 
Back
Top