+ Reply to Thread
Results 1 to 10 of 10

How to get MIN and MAX of Consecutive Numbers

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    How to get MIN and MAX of Consecutive Numbers

    Now here is a tricky one
    I have a table with the following numbers:
    A1 B1
    10 4115
    10 4116
    10 4117
    10 4118
    11 4125
    11 4126
    11 4127
    15 4150
    15 4151
    15 4152
    15 4153

    I need to have an output as shown below:
    C1 D1 E1
    10 4115 4118
    11 4125 4127
    15 4150 4153

    So basically, I need to group these numbers if they are consecutive and then get MIN and MAX for that set of numbers.
    Last edited by Livmi; 05-11-2015 at 12:39 PM.

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: How to get MIN and MAX of Consecutive Numbers

    See attached for a possible solution.
    Attached Files Attached Files
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: How to get MIN and MAX of Consecutive Numbers

    Thank you for helping me,

    It was a bad example for my problem,

    What if,
    10 4111
    10 4112
    10 4113
    10 4120
    10 4121
    10 4122
    11 4000
    11 4001
    11 4002
    11 4010
    11 4011
    11 4012


    Result desired would be
    10 4111 4113
    10 4120 4122
    11 4000 4002
    11 4010 4012
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: How to get MIN and MAX of Consecutive Numbers

    Can you explain the bins for me? As it is setup now, you are no longer pulling the max and min values. This complicates this process.

  5. #5
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: How to get MIN and MAX of Consecutive Numbers

    I know, and i am sorry for my first example, i just can't find a way to make it work, i've attached also a list with raw data.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: How to get MIN and MAX of Consecutive Numbers

    Attaching the data isn't helpful if you don't also explain what is needed.

  7. #7
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: How to get MIN and MAX of Consecutive Numbers

    Here it is another example. I have two columns A1 is ID, and B1 is ID_NB
    ID ID_NB
    10 4115
    10 4116
    10 4117
    10 4118
    10 4125
    10 4126
    10 4127
    11 4110
    11 4111
    11 4112
    11 4113
    11 4125
    11 4126
    11 4127
    15 4150
    15 4151
    15 4152
    15 4153

    What I need is to group numbers from ID_NB only if them are consecutive, based on ID, extracting all the consecutive intervals only and the afferent ID.
    For the above list, desired result would be:
    ID Small Big
    10 4115 4118
    10 4125 4127
    11 4110 4113
    11 4125 4127
    15 4150 4153

    I don't even know if it's posible to do it in excel, i hope that I explained myself better this time.
    Thank you once again for you patience.

  8. #8
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: How to get MIN and MAX of Consecutive Numbers

    It's possible, but probably best done with VBA.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: How to get MIN and MAX of Consecutive Numbers

    Here is a VBA solution to achieve this.
    Please find the workbook and click on Click Here button on the sheet to get the desired output in columns D, E and F.
    Before clicking on the button, enable the macro while prompted after opening the file.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  10. #10
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    11

    Re: How to get MIN and MAX of Consecutive Numbers

    First of all thank you for trying to help me out, sktneer, I am a beginner with VBA, i saw your code, and I think it's close to the result I want to achieve, my fault again, because I didn't explain my problem very well, so I've attached one more time, my sample data with the desired result on second sheet. Do you think is any chance to achieve that?

    Regards,
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Consecutive numbers
    By newx in forum Excel General
    Replies: 8
    Last Post: 09-08-2015, 09:04 PM
  2. Replies: 6
    Last Post: 03-23-2012, 06:03 PM
  3. Excel macro to "fill in" consecutive numbers in non-consecutive list?
    By Tomkat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2009, 01:13 PM
  4. Consecutive numbers
    By JamesChamp in forum Excel General
    Replies: 5
    Last Post: 03-17-2006, 08:11 AM
  5. [SOLVED] consecutive numbers
    By Monique in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-07-2005, 02:06 AM

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