Rows Between Next Item

[Shanelevene]

Hey everyone, I'm new here so just a short Hello and hoping you're all well.

I have a one column list of over 3000 rolls of a dice. E.g

3
6
5
3
2
3
4
4
2
2
3
etc, etc

I need a way to know how many rolls before the same number came up again. So, in the above snippet of the data, if I checked #3 it would return a result of: 3, 2, 5 (from the 1st #3 to the 2nd was 3 rolls, from the 2nd to 3rd was 2 rolls and from the 3rd to the 4th was 5 rolls). I could of course count all this manually but going through a data set of 3000 figures 6 separate times would be very time consuming and just one error could throw the statistics off. Rows between each matching item would also work, I'd just have to add +1 to each value returned.

Can anyone help me with a formula that could do this for me?

Many Thanks in advance, Shane.

 

[JustynaMK]

Hi Shane,

Not sure if this simple solution would work for you, but assuming that your list is in column A (A1:A3000), how about simply pasting the following formula into cell B1 & drag it down:
=MATCH(A1,A2:$A$3000,0)
For each row you will the number of rolls that happen before the same number came up again.

 

[Shanelevene]

Thank You Justyn, that worked perfectly.

 

[JustynaMK]

Great! Thanks for letting me know.

Take care,
Justyna