Eliminate sequential numbers

Lukhanyo

New Member
Joined
Jul 27, 2019
Messages
9
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Here's a brute force solution - not particularly elegant but it should do the job.

Code:
=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 :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top