Results 1 to 8 of 8

Thread: account how many cells between same category values in the column of values.

  1. #1

    account how many cells between same category values in the column of values.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Thanks for reading this. Im looking for a way to account intervals ( number of sells between same value(s) in one column). I need them ( intervals) to be posted in next column . Is there any formula for that? Its like column A has stream of numbers and l need to look how many cells are between every apiarence of number 26 in column A and values (number of cells between ) beeng posted in column B. For exemple, column A has numbers 26, 0, 32, 15, 5, 26, 0, 26, 8... first interval for number 26 will be of value 4 ( 4 cells between), second will be of value 1 ( just one cell between).
    Im using excel 2010, windows xp.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Assuming your list of numbers srtarts in A2, then in cell B2 place:
    =IFERROR(MATCH(A2,A3:$A$10,0)-1,"")
    and copy down.
    The $A$10 should be adjusted to the bottom cell of your list.

  3. #3
    Quote Originally Posted by p45cal View Post
    Assuming your list of numbers srtarts in A2, then in cell B2 place:
    =IFERROR(MATCH(A2,A3:$A$10,0)-1,"")
    and copy down.
    The $A$10 should be adjusted to the bottom cell of your list.
    Thank you very much for your answer. Ill try it when arrive home. You gave me hope

  4. #4
    I would like to know, if there is any way to make it adjust itself till the bottom of the list [autoadjusting for new entries ]? And how to make it account for multiple values, instead of one only. For example if l want to formula calculate skips between entries of same group of values,where any number of same group treated as number 26 on example above. 26,27,28 for exemple

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by sergiy View Post
    I would like to know, if there is any way to make it adjust itself till the bottom of the list [autoadjusting for new entries ]?
    Yes, you can do something like;
    =IFERROR(MATCH(A2,A3:$A$3500,0)-1,"")
    where the 3500 is beyond the bottom of your list and not encroaching on anything else.

    Or you can do something like:
    =IFERROR(MATCH(A2,OFFSET(A3,0,0,COUNTA(A3:A$1048576)),0)-1,"")


    Quote Originally Posted by sergiy View Post
    And how to make it account for multiple values, instead of one only. For example if l want to formula calculate skips between entries of same group of values,where any number of same group treated as number 26 on example above. 26,27,28 for exemple
    This is much more difficult; 2 questions, is this what you're looking for (click on the thumbnail to see a larger image):
    Click image for larger version. 

Name:	2014-08-05_180830.jpg 
Views:	7 
Size:	19.5 KB 
ID:	2529
    2. Would a user defined function do? This would make the entry of the formula much easier; it might be something like =GapSize($E$1:$E$3,A2:A$100000).
    Last edited by p45cal; 2014-08-05 at 07:26 PM.

  6. #6
    Quote Originally Posted by p45cal View Post
    Yes, you can do something like;
    =IFERROR(MATCH(A2,A3:$A$3500,0)-1,"")
    where the 3500 is beyond the bottom of your list and not encroaching on anything else.

    Or you can do something like:
    =IFERROR(MATCH(A2,OFFSET(A3,0,0,COUNTA(A3:A$1048576)),0)-1,"")


    This is much more difficult; 2 questions, is this what you're looking for (click on the thumbnail to see a larger image):
    Click image for larger version. 

Name:	2014-08-05_180830.jpg 
Views:	7 
Size:	19.5 KB 
ID:	2529
    2. Would a user defined function do? This would make the entry of the formula much easier; it might be something like =GapSize($E$1:$E$3,A2:A$100000).
    Yes,this image show exactly what l need. I am ok with user defined function. Just do not know how operate this type of functions yet.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    in B2 (with the group in E1:E3 as in the image):
    =IF(ISNA(MATCH(A2,$E$1:$E$3,0)),"",MIN(IF(ISNA(MATCH($E$1:$E$3,OFFSET(A3,0,0,COUNTA(A3:A$20000)),0)),FALSE,MATCH($E$1:$E$3,OFFSET(A3,0,0,COUNTA(A3:A$20000)),0)))-1)
    entered with Ctrl+Shift+Enter (aka array-entered) and copy down.
    A devil to manage a formula of that length.

    So a udf solution:
    In B2 the normally entered formula:
    =GapSize($E$1:$E$3,OFFSET(A2,0,0,COUNTA(A2:A$100000)))
    and copy down.

    Supported by this udf in a standard code-module:
    Code:
    Function GapSize(Look_Up, SearchIn)
    GapSize = ""
    If Not IsError(SearchIn) Then
      If SearchIn.Rows.Count > 1 Then
        LookupValues = Look_Up
        FirstCellIsOneOfLookUpValues = False
        For Each LookupValue In LookupValues
          If SearchIn.Cells(1) = LookupValue Then
            FirstCellIsOneOfLookUpValues = True
            Exit For
          End If
        Next LookupValue
        If FirstCellIsOneOfLookUpValues Then
          GapSize = "Last Instance"
          GS = 2000000#
          SearchInValues = SearchIn.Value
          For Each LookupValue In LookupValues
            I = 0
            For Each SearchInValue In SearchInValues
              I = I + 1
              If I > 1 Then
                If SearchInValue = LookupValue Or I >= GS Then
                  If I < GS Then GS = I
                  Exit For
                End If
              End If
            Next SearchInValue
          Next LookupValue
          If GS < 2000000# Then GapSize = GS - 2
        End If
      End If
    End If
    End Function
    Also in attached.
    Attached Files Attached Files

  8. #8
    p45cal , You Sir just made my day!!!! Or entire month!!! Very big THANK YOU !!!

Posting Permissions

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