Eliminate sequential numbers

[Lukhanyo]

Hello Friends,

I have a complex problem that I need to solve.

I would like to have the following results:

Column A has 5 digit numbers.

Of all the numbers that are consecutive in column A, I would like to have all of the digits that are consecutive deleted and print only unique non-consecutive 5 digit numbers in Column B.

Example:

1) Column A: 12345 Column B: (empty)
2) Column A: 13579 Column B: 13579
3) Column A: 12845 Column B (empty)
4) Column A: 13579 Column B: 13579

Previously I had received a formula to delete consecutive numbers but somehow it gets confused with the example below:

In Column B, no number must be consecutive to each other meaning any number added or subtracted by one would not be acceptable even if that combination is at the middle of the 5 digits, here is an example (EG: 14579, 13569 and 13578)

Your kind help and consideration dear MVP's is highly appreciated in advance.

Im new to the group hence sometimes I violate the code, my dear and sincere apologies for that.

Looking forward to your help, if this is not clear enough I'm available for further commentary.

Thank you.
Lukhanyo

 

[Gerald Higgins]

Here's a brute force solution - not particularly elegant but it should do the job.

=IF(OR(LEFT(A1,1)+0=MID(A1,2,1)-1,MID(A1,2,1)+0=MID(A1,3,1)-1),"",A1)

I've only set it up to compare the first digit to the second digit, and the second digit to the third digit.

If you like this approach, you can probably figure out how to extend it to the other digits.

ALSO, it only checks if digit 2 is 1 higher than digit 1, not 1 lower.
Again, you can probably figure out how to extend it to pairs of digits where the second is 1 lower than the first.


I have a feeling there should be a more elegant mathematical solution to this, I'll see if I can work one out.

Edit to add - missed out the actual solution first time round

 

[Eric W]

Try:

=IF(PRODUCT(--(ABS(MID(A1,{1,2,3,4},1)-MID(A1,{2,3,4,5},1))<>1)),A1,"")

 

[Gerald Higgins]

Eric W - neat, I thought there must be a more elegant solution, your's certainly is !

 

[Lukhanyo]

Thank you friends!

Solved!

 

[Eric W]

Glad we could help!

I think these kind of formulas are fun to figure out.

 

[Kenneth Hobson]

Extensively discussed: https://www.excelforum.com/excel-pr...consecutive-numbers-in-a-5-number-string.html