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.

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.

p45cal said:

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.

Click to expand...

Thank you very much for your answer. Ill try it when arrive home. You gave me hope

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

sergiy said:

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 ]?

Click to expand...

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,"")

sergiy said:

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

Click to expand...

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

p45cal said:

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):
View attachment 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).

Click to expand...

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.

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:
Also in attached.

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