On Jan 6, 10:22*am, Ron Rosenfeld wrote:
On Fri, 6 Jan 2012 05:22:31 -0800 (PST), JAgger1 wrote:
I've got 1000 data sets, I would like to remove the data sets that
have sequential numbers with in the set. Here's an example

A1: 1 2 5 7 9
A2: 1 3 5 7 9
A3: 1 3 4 5 8
A4: 3 4 6 7 9
A5: 2 4 6 8 0

How would I write a formula to remove any of the data sets (A1, A3,
A4) that have sequential numbers in it? Thanks

A formula cannot "remove a data set", it can only return a value. *To "remove a data set" you would have to use a VBA macro or one of the builtin filters. If you are going to use the Data/Filter, you MUST have a row of labels, so your data cannot start in Row 1 (I have adjusted my recommendation accordingly.

For a formula that can differentiate if there are sequential values in your five digit series, where a sequential number is defined by a following number being one more than the preceding:

This formula must be **array-entered**:

=OR((B2:E2-A2:D2)=1)

will return TRUE for sequential numbers, FALSE if not.

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. *If you did this
correctly, Excel will place braces {...} around the formula.

Then fill down as far as required.

You could then choose to use Data/Filter; filter on column F for the FALSE values.



Excellent! Thanks for the help everyone