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

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

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. 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. Originally Posted by p45cal
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. 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. Originally Posted by sergiy
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,"")

Originally Posted by sergiy
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):

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).

6. Originally Posted by p45cal
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):

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. 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.

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
•