+ Reply to Thread
Results 1 to 10 of 10

Sequence in numbering..

  1. #1
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Sequence in numbering..

    I'm in a tricky situation..
    I've been trying to minimize creating multiple parallel columns of IF(MAX) condtions and was wondering if there was a way to simply scan rightwards (X->Y->Z) and move on to the next row (i.e. in excel A7->B7->C7-> A6->B6->C6 -> A5->B5->C5).

    (please see excel sheet)

    Essentially, the trigger will start a continuos sequence in which

    -it checks if the next value is at least 98% or bigger than the previous one. If it is, then the process continues to the next Cell.
    **Also, if it's bigger then the last one, then it becomes the New Highest value in which all next values will be compared to.

    -Although, if it's lower then 2% of the value compared to, then it reports back that value in a subsequent column..

    Thanks a lot for your help
    Attached Files Attached Files
    Last edited by Exequiel3k; 05-25-2016 at 09:38 AM.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Sequence in numbering..

    The sample doesn't make any sense.

    Can you add a few more examples?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Re: Sequence in numbering..

    I re-did the excel, hope it helps
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Sequence in numbering..

    Hello Exequiel3k,

    Since I believed VBA will be a better approach to this problem, I went ahead and wrote a small one
    Please Login or Register  to view this content.
    Here is your sample with it, showing you the results. Simply put 1 in column E and press Run. (If you remove "1" from any row in column E, the macro will also delete the value in colun F and G of the same row)

    If you are still interested in an approach with only formula, are you fine with having helper columns?

    Edit: Fixed macro and file due to misreading
    Attached Files Attached Files
    Last edited by Lemice; 05-25-2016 at 12:45 AM.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  5. #5
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Re: Sequence in numbering..

    Thanks for the quick reply!

    Learning how to script in VBA is in my todo but for the moment I can't reliably read the script and know what to modifiy in case some things have to be changed down the line.

    Its more than fine having helper columns. Thx!
    Last edited by Exequiel3k; 05-25-2016 at 11:04 AM.

  6. #6
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Re: Sequence in numbering..

    I found a rudimentary way of having it execute the sequence:

    Please see Excel Redone-2 (I've just updated it @2:39 (ET))

    Also, any clues how I could formulate the lookup in which the searches for the 0 in the R.X (same row) and displays the value highest value (which should be the cell right before it) and reports that number..
    Thank you all
    Attached Files Attached Files
    Last edited by Exequiel3k; 05-25-2016 at 02:46 PM.

  7. #7
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Re: Sequence in numbering..

    I've simplied the procedure because it was too complicated having a starting point where it was..

    Here`s a simplified way of proceeding (see new excel)
    Attached Files Attached Files
    Last edited by Exequiel3k; 05-25-2016 at 03:22 PM.

  8. #8
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Sequence in numbering..

    Well, here's a messy way to do it, with ... 7 helper columns (Yes I know I'm lazy to merge them)

    Technically first I translate the table into a single column of value, then find the first row with "1" in column E, mark the value of column D in the row below as 1 as sequence beginner, then proceed to do a check on every single value against each other. The result will be the first value right before the first FALSE from that value up.

    It only works for one input at a time, so you will need to copy paste value, and put "1" from the bottom up (1 in E7, copy paste value in F7:G7 into themselves to save the value, then put 1 in E6, so on and so forth)

    If you think this is good enough, you can always make more helper columns for the 2nd, 3rd, 4th, ... "1" value in column E, making them calculating seperatedly and you can get the corresponding results by manipulating COUNTIF, INDIRECT, ROW(), COLUMN(), etc

    I'll try again later to see if I can make it so that if you put 1 in any row, it will return the value instantly, unlike this. But yes I doubt that it will be any better lol. I made this first to determine how many steps I need to take before I can point out the exact result anyway.
    Attached Files Attached Files
    Last edited by Lemice; 05-26-2016 at 12:23 AM.

  9. #9
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Sequence in numbering..

    Here's another shot at it, just put 1 on any row in "E" column, using 5 helper columns.

    I think there will be cases where it went through the logic net, but I can't find any atm. Let me know if you find one.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Re: Sequence in numbering..

    Thanks for the feedback Lemice

    I did parallel rows for the sequence and Hid it all to not look saturated :p
    Have a good day!

+ 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. Change numbering on column from topic to in sequence
    By Shamz41 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-06-2014, 02:24 PM
  2. [SOLVED] Sequence numbering with 3 result combinations
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-10-2014, 06:46 AM
  3. [SOLVED] Numbering Sequence based on two criteria.
    By SamCV in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-13-2013, 02:16 AM
  4. Sequence numbering for each duplicate record
    By excel.help in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2011, 05:48 PM
  5. Numbering a sequence
    By nods in forum Excel General
    Replies: 4
    Last Post: 10-06-2010, 11:00 AM
  6. Automatic Sequence Numbering with Autofilter
    By igi0814 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-18-2009, 03:37 AM
  7. Numbering Sequence
    By scw1217 in forum Excel General
    Replies: 1
    Last Post: 02-23-2009, 05:51 PM

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