Removing Sequential Numbers

  • Thread starter Thread starter JAgger1
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
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.
Click to expand...

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.
 
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 -
Click to expand...

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
 
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-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.
Click to expand...



Excellent! Thanks for the help everyone
 
Back
Top