+ Reply to Thread
Results 1 to 3 of 3

Find all consecutive numbers in range

  1. #1
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Find all consecutive numbers in range

    Hi guys,

    I am trying to find the consecutive numbers in a range but I only manage to get the result for one consecutive number only.

    For example,

    with range 1,2,3,5,6,9,18,19 the result should be 3-2-2 because there are 3 consecutive numbers (1,2,3), then 2 consecutive numbers (5,6), then another 2 consecutive numbers (18,19)

    A few more examples,

    with range 1,2,5,6,8,9,11,12 the result would be 2-2-2-2
    with range 1,2,3,4,7,8,10,13 the result would be 4-2
    with range 1,2,3,5,6,9,10,12 the result would be 3-2-2

    If this can be done with either a macro or a formula it would be great. Thank you for looking!

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Find all consecutive numbers in range

    Hi

    Are the values in different cells (say A1:H1) or are they all in a single cell, with a comma separator?

    If they are in the range A1:H1, then put the following in a general module
    Please Login or Register  to view this content.
    In J1 enter the formula
    Please Login or Register  to view this content.

    rylo
    Last edited by rylo; 10-09-2012 at 07:35 PM.

  3. #3
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Find all consecutive numbers in range

    Hi rylo,
    Thank you very much for your help. The function works absolutely great! Exactly what I was looking for! Thanks a bunch for your help!
    P.S Sorry for the late reply, I was at home when I posted the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1