Eliminate consecutive numbers in a 5 number string

Try changing this...

Please Login or Register  to view this content.

to...

Please Login or Register  to view this content.

Thanks a lot for the suggestion. Unfortunately it yielded no results.

Perhaps it would do me good to know what each line of the code means, from then I can try play with it, id appreciate knowing that from you.

Thanks in advance.

Please show example cell data and expected results. The link's code input value are delimited by space characters. If your data is not that, then code will need to be changed.

Or, did you want to generate a random 5 digit integer number with no two digits increasing by one? e.g. A1=13213, but not A1=12231.

Or, if A1=12313 then return B1=True or if A1=12231 then B1=False.

This is an example of desired results:

LIST EXPECTED RESULT
16 27 33 37 16 27 33 37
18 19 20 21 01 19 20 32
01 19 20 32 08 26 32 39
08 26 32 39 13 14 24 25
36 37 38 39 03 04 30 31
13 14 24 25 05 20 30 32
03 04 30 31 06 25 36 38
01 02 03 04 12 13 28 31
34 35 36 37 01 04 17 19
05 20 30 32 09 31 36 38
18 19 20 21 27 28 33 34
06 25 36 38 08 09 19 33
12 13 28 31 02 06 24 33
01 04 17 19 13 30 33 35
09 31 36 38 19 21 27 35

Im sorry the example above is not formatted properly.

The idea is that, if column A has a combination of consecutive numbers, then those are not printed out on column B.

Therefore column B will only comprise of unique and non-consecutive 5 digit numbers.

Column A: 12345 Column B: no print
Column A: 13579 Column B: 13579

something of this nature.

Thank you

Try changing the <> in this line to =

Please Login or Register  to view this content.

Perhaps it would do me good to know what each line of the code means, from then I can try play with it, id appreciate knowing that from you.

Look at your Mr. Excel post

Thanks for all the information, unfortunately it gives same results.

It seems I am not preview to add Excel attachments as yet, can I have your Email via the direct message so I can send it over for you?

Thank you.

Please Login or Register  to view this content.

Thank you very much jindon,

What this code did, is to copy column A and paste in on column B.

Sadly no change as yet.

Upload your workbook.

Originally Posted by Lukhanyo

I have some help please, how can I manipulate it that it only gives me a unique set of 5 non-consecutive numbers only, such as 13579 instead of 12345?

If data in colA is just a 5 digits number

Please Login or Register  to view this content.

thank you very much for your replies!

At: bakerman2 I get an error

At: jiodon the code simply copies column A to Column B. Yes I would really love to upload my workbook but at this time the forum does not allow me to make such abs upload. Hence I thought of the direct messaging option.

thanks in advance for considering!

kind regards.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

hello friends.

please find workbook attached for your consideration.

thank you.

I have attached the full file in this second attachment post, thanks for the tips on how to attach a workbook.

Kind regards.

You don't have consecutive numbers like 1 2 3 4 5, so my first code outputs all the data in col.B.

What is your desired result?

↑
People from that country doesn't seem to know internet manner to pay respect to the author...

Seems no change...

At Kenneth Hobson, yes yes yes thats exactly what im looking for.

Thank you for bearing with my weaknesses all of you dear kind friends.

Hello my Guru friend jindon,

from my side this one doesn't give the desired result.

The desired result is as follows:


Input: 1 2 4 22 23
Output: 4

Input: 22 24 26 22 3
Output: 22 24 26 3

Input: 1 3 5 1 3
Output: 1 3 5

1. The next number can not be one more than the previous.
2. No duplicate numbers.

Thank you very much for the help in advance.

Very sorry Bakerman2, this one produced no results.

Thank you very much for trying.

Originally Posted by Lukhanyo

2. No duplicate numbers.

According to your post #18 attachment that's irrelevant as it has no duplicate !
If you change rules or data, just update accordingly the formulas within the next demonstration …

An Excel beginner can achieve this without any code just with formulas in helper columns
so according to your post #18 full file I apply some formulas in this starter demonstration :

PHP Code: 

Sub Demo1()
         Application.ScreenUpdating = False
    With [A1].CurrentRegion.Columns(1)
        .Item("B:M").Clear
        .TextToColumns [D1], xlDelimited, Space:=True
        .Item("I:M").Formula = Array("=IF(E1-D1>1,D1,"""")", "=IF(AND(ISNUMBER(I1),F1-E1>1),E1,"""")", _
              "=IF(AND(F1-E1>1,G1-F1>1),F1,"""")", "=IF(AND(G1-F1>1,ISNUMBER(M1)),G1,"""")", "=IF(H1-G1>1,H1,"""")")
        .Item(2).Formula = "=TRIM(I1&"" ""&J1&"" ""&K1&"" ""&L1&"" ""&M1)"
        .Item(2).NumberFormat = "@"
        .Item(2).Formula = .Item(2).Value2
        .Item("D:M").Clear
    End With
         Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Is this what you want?
I originally worked with the logic in the link that you have provided, so this is not related that one.

Please Login or Register  to view this content.

No need any Dictionary :

PHP Code: 

Sub Demo2()
        Dim V, R&, S
    With [A1].CurrentRegion.Columns
            V = .Item(1).Value2
        For R = 1 To UBound(V)
            S = Split(V(R, 1))
            If S(1) - S(0) > 1 Then V(R, 1) = " " & S(0) Else V(R, 1) = ""
            If S(1) - S(0) > 1 And S(2) - S(1) > 1 Then V(R, 1) = V(R, 1) & " " & S(1)
            If S(2) - S(1) > 1 And S(3) - S(2) > 1 Then V(R, 1) = V(R, 1) & " " & S(2)
            If S(3) - S(2) > 1 And S(4) - S(3) > 1 Then V(R, 1) = V(R, 1) & " " & S(3)
            If S(4) - S(3) > 1 Then V(R, 1) = V(R, 1) & " " & S(4)
        Next
           .Item(2).Value2 = V
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

IT WORKED thank you thank you and thank you!

now I need a code that will do the following.

If Block B has 5 digits print them over to Block C.

If Block B has less than 5 digits, do not do any action.

Thank you.

Originally Posted by Lukhanyo

IT WORKED

Which code are you talking about?

The results are different.

so now the code you helped me with printed the desired results, thank you for that!

No I am speaking of a second one, since the results are printed in Column B, theres a lot to sort, I am only interested in the 5 digit results.

Is it possible to write a piece of code that will take all 5 digit numbers from Column B and copy them over to Column C, I have attached the workbook.

Thanks in advance for the work.

This is the code that worked:

Sub Demo1()
Application.ScreenUpdating = False
With [A1].CurrentRegion.Columns(1)
.Item("B:M").Clear
.TextToColumns [D1], xlDelimited, Space:=True
.Item("I:M").Formula = Array("=IF(E1-D1>1,D1,"""")", "=IF(AND(ISNUMBER(I1),F1-E1>1),E1,"""")", _
"=IF(AND(F1-E1>1,G1-F1>1),F1,"""")", "=IF(AND(G1-F1>1,ISNUMBER(M1)),G1,"""")", "=IF(H1-G1>1,H1,"""")")
.Item(2).Formula = "=TRIM(I1&"" ""&J1&"" ""&K1&"" ""&L1&"" ""&M1)"
.Item(2).NumberFormat = "@"
.Item(2).Formula = .Item(2).Value2
.Item("D:M").Clear
End With
Application.ScreenUpdating = True
End Sub

attached is a picture of Column A, and the results of the formula in Column B.

Now I want all the digits that are less than 5, to remain in Column B. and all the digits that are 5 in number to print in Column C.

Example:

Column A: 1234 Column B: 1234 Column C: (empty)

Column A: 12345 Column B: 12345 Column C: 12345.

Thank you

If result in col.B has 5 numbers, copy to col.C?

Please Login or Register  to view this content.

Hello Friend,

It gives this error: Run-time error '429': ActiveX component can't create object.

thanks in advance for assistance.

Originally Posted by Lukhanyo

Is it possible to write a piece of code that will take all 5 digit numbers from Column B and copy them over to Column C, I have attached the workbook.

You must explain all your need when you create the thread
as it can be a waste of time for any helper to rewrite a code for any new change !
As per forum rules a new question, a new thread …

So I hope at least it's now your complete need (?) or explain at least all you need.

xl2012....

Try this one

Please Login or Register  to view this content.

Thanks Marc L,

This one is throwing an error. . . Run-time error '9' Subscript out of range.

thank you

THANK YOU THANK YOU THANK YOU THANK YOU,

you are an EXCEL GURU.

You are welcome and thanks for the multiple reps.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

thanks jindon, I noticed in my original post that there was some details missing in the output, this threat has been going on for a while I will close it as requested and start a new one.

Originally Posted by Lukhanyo

Thanks Marc L,

This one is throwing an error. . . Run-time error '9' Subscript out of range.

No error on my side with your attachment so did you try with my post #41 code second version ?

Cross-posted with formula solutions using input of "12345": https://www.mrexcel.com/forum/excel-...l-numbers.html