+ Reply to Thread
Results 1 to 11 of 11

Conditional Sequential Numbering

  1. #1
    Registered User
    Join Date
    03-26-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 16.34 (20020900)
    Posts
    23

    Conditional Sequential Numbering

    hi

    I have a sheet which records win and losses. Here's my dilemma:-

    Column I displays the results, so it has either Win or Lost.

    Column A has the sequence number.

    The idea is that sequences restart after 4 Win or 1 Lost. The number would be like this

    1 Win
    1 Win
    1 Win
    1 Win
    2 Win
    2 Lost
    3 Lost
    4 Win
    4 Win
    4 Win
    4 Win
    5 Lost
    6 Lost
    7 Win
    7 Win
    7 Lost
    8 Win

    And so on. So you see that each sequence has the next number and a sequence starts after either 4 showing as Win or 1 showing as Lost

    Any thoughts on how to best approach this one? I looked at the SEQUENCE function, but wasn't able to get that to behave as I wanted.

    I have tried using =COUNTIF($I$1:I1,I4) but that was not helping. I thought to try and compare existing row to the previous, but of course, where there are more than 2 occurrences of Lost, it would count them as a sequence, rather than restarting the numbering

    Thanks in advance

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Sequential Numbering

    I think your 1st LOST number is wrong, shouldnt that be 3 (like 5)? Actually, if I understand your rules, 1st 7 is wrong and so is last 7, as well as 8...you said 4 wins for the next number, correct?

    If to, then try this - assuming your data started in row 2...
    fill in A2:A4 manually
    A5=IF(I5="Lost",A4+1,IF(COUNTIF(I1:I5,"win")<=4,A4,A4+1))
    copied down

    (could probably combine that into 1 IF)
    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
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Sequential Numbering

    OK simplified version...
    A5=A4+IF(OR(I5="Lost",COUNTIF(I1:I5,"win")>4),1+0)

  4. #4
    Registered User
    Join Date
    03-26-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 16.34 (20020900)
    Posts
    23

    Re: Conditional Sequential Numbering

    cheers Ford. Thanks for the reply

    No, the idea is that the sequence needs to reset AFTER any loss or 4th win, so any sequence will end either with a 4th Win or a 1st Lost. Does that makes sense? So those you list are all correct.

    One thing I omitted to say was that the first numbers and Win / Lost don't start until row 3. There is a blank row due to some graphics and then a header row.

    The COUNTIF in your formula appears to be looking for 5 win in a row, which is not the gist of it.

    I guess just in English, a new sequence commences after 4 wins, or after 1 loss. Each time that occurs, the sequence should move onto the next number in order.

    I hope that clarifies it somewhat and thanks heaps for taking the time

    Regards

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Conditional Sequential Numbering

    The following produces the same numbers as shown in post #1 (beginning in cell A3): =IF(ROW()=3,1,IF(OR(I2="Lost",COUNTIFS(A$2:A2,A2,I$2:I2,I2)=4),SUM(A2,1),A2))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    03-26-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 16.34 (20020900)
    Posts
    23

    Re: Conditional Sequential Numbering

    cheers JeteMc

    That works a treat, creating the next number in sequence immediately after either a Lost or 4th Win. Brilliant.

    I may be stretching the friendship here, but now that the sequences are determined, is there any way to tally up the profit or loss figures based on those sequences? The profit or loss amount of each selection is in column K, with a number in each cell. I'd like to be able to tally up a profit or loss for each sequence.

    I have been trying to work out the structure of the formula, as I don't think it would be that difficult. The formula would be looking for matching numbers in column A and then adding up the corresponding numbers in column K. I have been trying to see if SUMIF or SUMIFS would do the trick. Sort of, add the cells in column K if the cells in column A match.

    I have this which does the calculation =SUMIF(A:A,A3,K:K), but it puts the result in each cell, which naturally means the same total is in each cell of the sequence, making the results incorrect.

    How is it possible for that formula to work only on 1 cell of a sequence, first or last? Last is probably preferable

    cheers

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Conditional Sequential Numbering

    I believe that I understand the layout you are describing, however to be sure please upload a sample that includes the desired results manually calculated and displayed, so that we have something to go by when writing formulas/code.
    Let me also ask if a pivot table would be acceptable? I feel that putting the sequence in the Rows field and the profit/loss figures in the Values field (Sum of) would likely produce what you want.
    Let us know if you have any questions.

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Conditional Sequential Numbering

    Try this. It is slower.

    =IF(COUNTIF($A$2:A3,A3)=1,SUMIF(A:A,A3,K:K),"")
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  9. #9
    Registered User
    Join Date
    03-26-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 16.34 (20020900)
    Posts
    23

    Re: Conditional Sequential Numbering

    Thanks so much. That did the trick and it all works perfectly now.

    cheers
    Last edited by AliGW; 03-01-2020 at 06:08 AM. Reason: Please don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,328

    Re: Conditional Sequential Numbering

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

    Also, please update your user profile if you are using a newer version of Excel than 2010.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Registered User
    Join Date
    03-26-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 16.34 (20020900)
    Posts
    23

    Re: Conditional Sequential Numbering

    Thanks Ali. Marked as solved and updated my Excel version. cheers

+ 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. Sequential Numbering
    By chipd512 in forum Excel General
    Replies: 1
    Last Post: 11-25-2015, 03:39 PM
  2. Sequential numbering
    By taylorackley in forum Excel General
    Replies: 2
    Last Post: 08-26-2010, 02:19 PM
  3. [SOLVED] VBA Conditional Sequential Numbering
    By Jester0001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2010, 01:17 AM
  4. Sequential numbering
    By mojura in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2009, 01:52 PM
  5. Sequential numbering
    By antony moseley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-08-2009, 09:49 AM
  6. Sequential Numbering
    By Tom in forum Excel General
    Replies: 4
    Last Post: 08-21-2006, 05:25 PM
  7. Sequential Numbering.
    By Rodney in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-02-2005, 05:06 PM

Tags for this Thread

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