count until "X" number appears again
Results 1 to 10 of 10

Thread: count until "X" number appears again

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default count until "X" number appears again

    Hi Mr. Excel'ers,

    So, here's my problem. I have a list of numbers in Col A.. from A2 on down, and growing. I continually add numbers to the bottom of the list, so it's a growing list. I'm at A345 now. next number I add will be A346.

    Say for example I add number 6 to A346. Then in A347 I add 8, 348 I add 10, 349 I add 17 and so on until in A355 I add 6 again. I need to know the count of numbers from A346 to A355. Then the process starts over until 6 appears again, giving the count of numbers between A355 to the next time 6 is entered.

    I would like the formula to be able to entered on a different sheet then where the number list is.... is this doable? and does all this make sense??

    Thanks for your help!!

    Dave.

  2. #2
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    i've seen this done with a LOOKUP(), i think, but here's my approach

    This approach is sensitive and assumes that the different sheet has the same structure as Sheet1

    But if you put this formula in any cell in Row 2 in your second sheet, and enter it with CTRL+SHFT+ENTER it will work.

    =IF(COUNTIF(Sheet1!$A$2:A3,6)<2,"Not Enough 6s",IF(Sheet1!A3=6,ROW()-MAX((--(Sheet1!$A$2:A2=6))*ROW(Sheet1!$A$2:A2)),"cell not 6"))

    This cell can then be copied down and will work.
    "I'm almost sure I'm not mad..." - Stoppard

  3. #3
    New Member
    Join Date
    Mar 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    Thank you Glove_Man for your reply.. I was unable to get your formula to work for me?? I know I had to change some things in the formula like sheet name etc...

    I wish I could upload an example file for you to look at. I think that would help.

    List sheet:
    A
    2
    5
    10
    12
    6
    9
    11
    15
    1
    10
    13
    3
    5
    13
    8
    14
    7
    4

    in the table above, on the List sheet, the last number entered is 4, and that is at the bottom of my number list at position A346.

    On another sheet, the Tracking sheet, I have the numbers 1 to 15 listed down from A2 down to A15. In the B column I have the counts. see table below:
    # Counts
    1 9
    2 17
    3 6
    4 0
    5 5
    6 13
    7 1
    8 3
    9 12
    10 8
    11 11
    12 14
    13 4
    14 2
    15 10

    so, in the table above i have the number 1 thru 15 listed in A column and the counts listed in B. the counts tracks how many times ago the number was last entered... so, the number with a count of 0 is 4, indicating it was the last number entered on A346 on the List sheet.
    the number 15 was entered 10 numbers ago on the list sheet, 14 was entered 2 numbers ago etc.....

    If the next number added to the list is 14 for example, then a zero will be entered in the count column next to 14. and all other counts will increase by 1... 15 will be 11, 13 will be 5, 12 will be 15 ......4 will go to 1

    hope this helps.

    Thanks again for any and all help offered.

    Dave

  4. #4
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    Ahhh.. I slightly misunderstood....

    Is VBA an option? This is very easy in VBA, but very hard using regular Excel formulas.

    I could do it using a couple of extra columns of "workings" calculations.
    "I'm almost sure I'm not mad..." - Stoppard

  5. #5
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    Try this instead.

    =COUNT(Sheet1!$A$2:$A$500)+1-MAX(--(Sheet1!$A$2:$A$500=A2)*ROW(Sheet1!$A$2:$A$500))

    Still an array formula - enter with Ctrl+Shft+Enter

    You'll notice i've used a round 500 in there.... It's OK, the formula ignores empty cells. BUt you'll need to make sure the space below the entered numbers is empty. And, the formula has two weaknesses....

    (1) It will stop working when the list gets beyond 500. You can change all the 500 to 1000 (or any number) if you want.
    (2) if you put a number in Column A below the data, or leave rows blank, it will much things up.
    "I'm almost sure I'm not mad..." - Stoppard

  6. #6
    New Member
    Join Date
    Mar 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    Thank you!! This worked perfectly, does exactly what I needed.

    Do you think you could break down and explain how this formula works, so that in the future I might be able to figure out how to do this myself?

    thanks again for all your help, much appreciated!

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: count until "X" number appears again

    If you are interested in a formula that doesn't require the Ctrl+Shift+Enter confirmation and also doesn't matter if there were blanks within the column A numbers then you could try this. Like Glove_Man's formula, you just need to make sure that the $500 is big enough to cover wherever your data might end up.

    Tracking

    AB
    1NumberLast
    219
    3217
    436
    540
    655
    7613
    871
    983
    10912
    11108
    121111
    131214
    14134
    15142
    161510

    Spreadsheet Formulas
    CellFormula
    B2=LOOKUP(9.99E+307,List!A$2:A$500,ROW(List!A$2:A$500))-AGGREGATE(14,6,ROW(List!A$2:A$500)/(List!A$2:A$500=A2),1)


    Excel tables to the web >> Excel Jeanie HTML 4


    .. or if you want to save Excel having to calculate the same thing (where the last row of data is) multiple times, you could put that part in a vacent cell like this.

    Tracking (2)

    ABCD
    1NumberLast 346
    219
    3217
    436
    540
    655
    7613
    871
    983
    10912
    11108
    121111
    131214
    14134
    15142
    161510

    Spreadsheet Formulas
    CellFormula
    D1=LOOKUP(9.99E+307,List!A$2:A$500,ROW(List!A$2:A$500))
    B2=D$1-AGGREGATE(14,6,ROW(List!A$2:A$500)/(List!A$2:A$500=A2),1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 22nd, 2019 at 06:39 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    Quote Originally Posted by Glove_Man View Post
    =COUNT(Sheet1!$A$2:$A$500)+1-MAX(--(Sheet1!$A$2:$A$500=A2)*ROW(Sheet1!$A$2:$A$500))
    The COUNT() simply counts up how many entries, of any sort, are in the range A2:A500.

    The MAX() part is the tricky bit.

    Sheet1!$A$2:$A$500=A2 returns an array of TRUE/FALSE responses as to whether the list equals the value specified in the second sheets A2.
    Putting the -- infront of the array means the TRUE/FALSE are instead expressed as 1/0. So you end up with a list that looks like (0,0,0,1,0,0,0,1,0,1,1,0,0,0,0,0,0,1) etc...

    ROW(A2:A500) just returns the row number of every cell, ie. (2,3,4,5,......,499,500)

    Multiplying (0,1,0,0,1...) by (2,3,4....) returns a list that now looks like (0,0,4,0,0,0,0,0,10,0,0,13) where the zeroes are instances of numbers other than the one desired, and the numbers are the row numbers of when the wanted number is used.

    The MAX() finds the largest row number where the the wanted number is used. Ergo, the most recent usage of the number.

    So if there are COUNT(...) numbers in the list, and the most recent usage of the wanted number is in row MAX(...) you subtract the two to find how many extra have been used. The +1 is there because the list starts in Row 2 rather than Row 1.

    Peter_SS's approach was the LOOKUP() one I could remember seeing but couldn't quite remember how to do.
    "I'm almost sure I'm not mad..." - Stoppard

  9. #9
    New Member
    Join Date
    Mar 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count until "X" number appears again

    Glove_Man,
    I really appreciate your help with the formula and then explaining it to me, thank you. Given some practice I might be able to put a formula like this together myself......maybe.

    Peter_SSs, Thank you for your input, I will check it out and see how it works.

    Dave

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: count until "X" number appears again

    Quote Originally Posted by dwrowe001 View Post
    Peter_SSs, Thank you for your input, I will check it out and see how it works.

    Dave
    No problem. Look forward to hearing if it also works for you.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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