We will be migrating from Ask to Discourse on the first week of August, read the details here

# Calc Rows between matching entries

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 (shows it as a row here, but it is a column).

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?

edit retag close merge delete

Sort by » oldest newest most voted

Mike, thanks for your time and reply. I just found the answer by creating a validity list and then using an IF and MATCH formula and then dragging it down. It works for all numbers as soon as I choose it from my validity list.

But thank you anyway. It looks like you was on the right path and not too far away from what I eventually did. It wasn't my cleverness which solved it I found a similar problem elsewhere on the web and struck gold with that.

more

Please edit the answer to include the formulas and steps to your answer, to make it useful to those who might seek for a similar solution. The goal of the site is not only to help those who ask a question, but also to keep solutions so that others could benefit.

( 2018-08-07 11:33:40 +0200 )edit

Assuming that your data is in column A, starting from A1: enter this formula to B3:

=ROW()-MAXIFS(ROW($A$1:$A2);$A$1:$A2;$A3)  Unfortunately, this doesn't work for B2 using formula like =ROW()-MAXIFS(ROW($A$1:$A1);$A$1:$A1;$A2) - I have just filed tdf#119137 for that; so for the second row, you'd need to count manually (first row, naturally, doesn't have previous rows, including matching).

more