Removing Sequential Numbers

[JAgger1]

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

 

[John Coleman]

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

You are not giving much to go on. Are you looking for a spreadsheet
formula or a macro? In either case - how is your data stored in the
spreadsheet? Are there always exactly 5 numbers? Are these numbers
stored in different cells? Etc.

For example - if there are always 5 numbers and they are stored in
columns A through E (with different data sets in different rows), then
you could somewhat easily write a spreadsheet formula to be placed in
column F which evaluates to zero if and only if the data set contains
sequential numbers, after which you could filter based on the values
in column F. But if the data sets are stored in strings which have a
label as a prefix (e.g. "A5: 2 4 6 8 0") then some preliminary parsing
of the data would be needed.

 

[JAgger1]

You are not giving much to go on.  Are you looking for a spreadsheet
formula or a macro? In either case - how is your data stored in the
spreadsheet? Are there always exactly 5 numbers? Are these numbers
stored in different cells? Etc.

For example - if there are always 5 numbers and they are stored in
columns A through E (with different data sets in different rows), then
you could somewhat easily write a spreadsheet formula to be placed in
column F which evaluates to zero if and only if the data set contains
sequential numbers, after which you could filter based on the values
in column F. But if the data sets are stored in strings which have a
label as a prefix (e.g. "A5: 2 4 6 8 0") then some preliminary parsing
of the data would be needed.

I'm looking for a spreadsheet formula.

Ahh, sorry, I should have written the example better.

A B C D E
1: 1 2 5 7 9
2: 1 3 5 7 9
3: 3 4 6 7 9
4: 2 4 6 8 0


The number are generated to a text file and then I import them into
Excel. There is always 5 numbers (but can range from 1 - 100). They
are stored in Column A thru E (but I can change that if needed) and I
currently have 1000 sets to sort thru.

 

[John Coleman]

I'm looking for a spreadsheet formula.

Ahh, sorry, I should have written the example better.

    A B C  D  E
1: 1  2  5  7  9
2: 1  3  5  7  9
3: 3  4  6  7  9
4: 2  4  6  8  0

The number are generated to a text file and then I import them into
Excel. There is always 5 numbers (but can range from 1 - 100). They
are stored in Column A thru E (but I can change that if needed) and I
currently have 1000 sets to sort thru.- Hide quoted text -

- Show quoted text -

In F1 place the formula

=IF((A1+1-B1)*(B1+1-C1)*(D1+1-E1)*(D1+1-E1)=0,0,1)

And copy it down through the following 999 rows. It will evaluate to 0
if there are successive numbers and 1 otherwise. You could then sort
the data based on column F to get all the rows with sequential numbers
at the top.

Note that my formula would not count e.g 2 4 3 6 8 as having
sequential numbers since 4 3 is not in sequential order. Similarly it
wouldn't count 1 3 5 2 6 as having sequential numbers. My formula is
designed to find consecutive numbers in the list which are in
sequential order. If this isn't adequate then you would need to have a
much more complicated formula (which uses absolute values and forms
all 4*5 = 20 possible differences). My formula works as you want on
the trial data that you supplied - but might fail if there are aspects
of the data you haven't specified.

Hope that helps

-John Coleman

 

[Ron Rosenfeld]

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-A22)=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.

 

[JAgger1]

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 recommendationaccordingly.

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-A22)=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