473,554 Members | 2,530 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to count consecutive numbers in a string of numbers

4 New Member
Can anyone help me with a Formula in VB/excel to COUNT how many numbers are consecutive
within a Row that spans 20 Columns?

Example:
65 67 68 69 75 79 80 84 85 90 78 73 61 93 92 91 95 6 33 99


The answer to the above example should be a Count of 11.
In order to illustrate what I mean to say that I require a total count of the
individual numbers that make up the consecutive. In the example above, we do the counting in this way: So, rather than 67/68 = 1
or 79/80 = 1.

I need 90 93, 92, 91 = 4
67,68,69 = 3
79,80 = 2
84,85 = 2
---
Total Count = 11
Is there a way that a Formula can be put together to count the above and to get the correct answer.
I'd like to this for an extremely large number list for each line, individually, in a spreadsheet like excel.


Regards, Tasmanian Devil
Oct 24 '10 #1
8 5336
TasmanianDevil
4 New Member
Is there someone in this forum that would like to share knowledge in helping to solve this puzzle?

I'm looking forward to receiving good news.
Nov 21 '10 #2
Guido Geurs
767 Recognized Expert Contributor
How :
- put the data in an array
- sort line by line
- find consecutive numbers
- dump data in sheet
This is the code for creating and sorting the array:

Expand|Select|Wrap|Line Numbers
  1. Public Sub Count()
  2. Dim ARRdata() As Variant
  3. Dim ARRdataROWidx As Integer
  4. Dim ARRdataCOLidx As Integer
  5. Dim ARRtemp(1 To 20) As Integer
  6.    Sheets("data").Select
  7.    ARRdata = Range("A1").Resize(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)
  8.    For ARRdataROWidx = LBound(ARRdata, 1) To UBound(ARRdata, 1)
  9.       For ARRdataCOLidx = 1 To 20
  10.          ARRtemp(ARRdataCOLidx) = ARRdata(ARRdataROWidx, ARRdataCOLidx)
  11.       Next
  12.       Call Sort_Array(ARRtemp)
  13.       For ARRdataCOLidx = 1 To 20
  14.          ARRdata(ARRdataROWidx, ARRdataCOLidx) = ARRtemp(ARRdataCOLidx)
  15.       Next
  16.    Next
  17. ...
  18. 'count consecutive numbers
  19. ...
  20. 'dump to ...
  21. ...
  22. End Sub
  23.  
  24. Public Function Sort_Array(ByRef THEarray As Variant)
  25. Dim TEMP As Variant
  26. Dim X As Integer
  27. Dim SORTED As Boolean
  28.    SORTED = False
  29.    Do While Not SORTED
  30.       SORTED = True
  31.       For X = 1 To UBound(THEarray) - 1
  32.          If THEarray(X) > THEarray(X + 1) Then
  33.             TEMP = THEarray(X + 1)
  34.             THEarray(X + 1) = THEarray(X)
  35.             THEarray(X) = TEMP
  36.             SORTED = False
  37.          End If
  38.       Next X
  39.    Loop
  40. End Function
Nov 21 '10 #3
TasmanianDevil
4 New Member
many thanks for your help but your formula displays an error message: it isn't possible for Visual Basic to determine which action to be performed.
And it says that the possible causes are: there is syntax error: punctuation or the data inputed are wrong and it isn't work out.
In vb the script stops:
an yellow arrow points here: Public Sub Count()

and only these 3 points [...] are marked in red

Next
Next
...
'count consecutive numbers
...
'dump to ...
...
End Sub
Nov 22 '10 #4
Guido Geurs
767 Recognized Expert Contributor
This is normal because it's the place where the code for the calculation must be placed.

In Your call You are giving the example and say it must be 11 ??? not 12 ???

65 67 68 69 75 79 80 84 85 90 78 73 61 93 92 91 95 6 33 99
67 68 69 79 80 84 85 90 78 93 92 91 = 12 !

the code for calculating =

Expand|Select|Wrap|Line Numbers
  1. Public Sub COUNT()
  2. Dim ARRdata() As Variant
  3. Dim ARRdataROW As Integer
  4. Dim ARRdataCOL As Integer
  5. Dim ARRtemp(1 To 20) As Integer
  6. Dim FIRST As Boolean
  7. Dim COUNT As Integer
  8. Dim ARRresults() As Integer
  9. '§ load and sort
  10.    Sheets("data").Select
  11.    ARRdata = Range("A1").Resize(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)
  12.    ReDim ARRresults(LBound(ARRdata, 1) To UBound(ARRdata, 1))
  13.    For ARRdataROW = LBound(ARRdata, 1) To UBound(ARRdata, 1)
  14.       For ARRdataCOL = 1 To 20
  15.          ARRtemp(ARRdataCOL) = ARRdata(ARRdataROW, ARRdataCOL)
  16.       Next
  17.       Call Sort_Array(ARRtemp)
  18.       For ARRdataCOL = 1 To 20
  19.          ARRdata(ARRdataROW, ARRdataCOL) = ARRtemp(ARRdataCOL)
  20.       Next
  21. '§ count
  22.       FIRST = True
  23.       COUNT = 0
  24.       For ARRdataCOL = 1 To 19
  25.          If ARRdata(ARRdataROW, ARRdataCOL) + 1 = ARRdata(ARRdataROW, ARRdataCOL + 1) Then
  26.             If FIRST Then
  27.                COUNT = COUNT + 2
  28.             Else
  29.                COUNT = COUNT + 1
  30.             End If
  31.             FIRST = False
  32.          Else
  33.             FIRST = True
  34.          End If
  35.       Next
  36.       ARRresults(ARRdataROW) = COUNT
  37.    Next
  38. '§ dump
  39.    Range("A20").Resize(UBound(ARRdata, 1), 20) = ARRdata
  40.    Range("V20").Resize(UBound(ARRresults), 1) = ARRresults
  41. End Sub    
I have dumped the results in A20... and V20...
If You want to see the results elsewhere, please change the code.
Nov 22 '10 #5
Guido Geurs
767 Recognized Expert Contributor
Sorry, there is an error in the code for ARRresults.

Expand|Select|Wrap|Line Numbers
  1. Public Sub COUNT()
  2. Dim ARRdata() As Variant
  3. Dim ARRdataROW As Integer
  4. Dim ARRdataCOL As Integer
  5. Dim ARRtemp(1 To 20) As Integer
  6. Dim FIRST As Boolean
  7. Dim COUNT As Integer
  8. Dim ARRresults() As Integer
  9. '§ load and sort
  10.    Sheets("data").Select
  11.    ARRdata = Range("A1").Resize(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)
  12.    ReDim ARRresults(LBound(ARRdata, 1) To UBound(ARRdata, 1), 1 To 1)
  13.    For ARRdataROW = LBound(ARRdata, 1) To UBound(ARRdata, 1)
  14.       For ARRdataCOL = 1 To 20
  15.          ARRtemp(ARRdataCOL) = ARRdata(ARRdataROW, ARRdataCOL)
  16.       Next
  17.       Call Sort_Array(ARRtemp)
  18.       For ARRdataCOL = 1 To 20
  19.          ARRdata(ARRdataROW, ARRdataCOL) = ARRtemp(ARRdataCOL)
  20.       Next
  21. '§ count
  22.       FIRST = True
  23.       COUNT = 0
  24.       For ARRdataCOL = 1 To 19
  25.          If ARRdata(ARRdataROW, ARRdataCOL) + 1 = ARRdata(ARRdataROW, ARRdataCOL + 1) Then
  26.             If FIRST Then
  27.                COUNT = COUNT + 2
  28.             Else
  29.                COUNT = COUNT + 1
  30.             End If
  31.             FIRST = False
  32.          Else
  33.             FIRST = True
  34.          End If
  35.       Next
  36.       ARRresults(ARRdataROW, 1) = COUNT
  37.    Next
  38. '§ dump
  39.    Range("A20").Resize(UBound(ARRdata, 1), 20) = ARRdata
  40.    Range("V20").Resize(UBound(ARRresults), 1) = ARRresults
  41. End Sub
Nov 22 '10 #6
smartchap
236 New Member
I think best way is to copy whole row in a new empty row or better in a new sheet at first row. arrange it in ascending order (by formula), now check each column one by one from first column in first row if its value is 1 more than previous, then increment the counter. Thats the result. For other rows of data do same thing.
Nov 23 '10 #7
TasmanianDevil
4 New Member
the last code that you provided isn't working. In vba some lines are appearing in red:
Dim*ARRdata()*A s*Variant
Dim*ARRtemp(1*T o*20)*As*Intege r
Dim*ARRresults( )*As*Integer
ReDim*ARRresult s(LBound(ARRdat a,*1)*To*UBound (ARRdata,*1),*1 *To*1)
***For*ARRdataR OW*=*LBound(ARR data,*1)*To*UBo und(ARRdata,*1)

*ARRtemp(ARRdat aCOL)*=*ARRdata (ARRdataROW,*AR RdataCOL)
**************A RRdata(ARRdataR OW,*ARRdataCOL) *=*ARRtemp(ARRd ataCOL)

If*ARRdata(ARRd ataROW,*ARRdata COL)*+*1*=*ARRd ata(ARRdataROW, *ARRdataCOL*+*1 )*Then
************If* FIRST*Then
ARRresults(ARRd ataROW,*1)*=*CO UNT
***
***Range("A20") .Resize(UBound( ARRdata,*1),*20 )*=*ARRdata
***Range("V20") .Resize(UBound( ARRresults),*1) *=*ARRresults

and excel isn't reconignizing that
there's a macro of this code.

merci beaucoup en avance
Dec 18 '10 #8
Guido Geurs
767 Recognized Expert Contributor
I'm using Excel 2003 and the macro runs without a problem.
(see attachment)
If Your using an other version maybe You have to translate this code to the new version.
Dec 19 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

8
3075
by: ishekara | last post by:
Hi all, An object of a class A which has no member variables and no member functions, still the size of the object is 1 byte, if there is a byte member variable then also the size of the object is 1 byte. Could anyone tell me the reason behind this. Thanks ishekara
0
7539
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7819
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8055
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7897
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5446
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5165
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3589
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1149
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
858
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.