Ask Your Question
0

Calc Rows between matching entries

asked 2018-08-07 08:22:30 +0200

ShaneLevene gravatar image

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?

Many Thanks in advance, Shane.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-08-07 10:23:07 +0200

updated 2018-08-07 10:24:01 +0200

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

edit flag offensive delete link more
0

answered 2018-08-07 11:24:36 +0200

ShaneLevene gravatar image

updated 2018-08-07 11:25:38 +0200

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.

edit flag offensive delete link more

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-07 11:33:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-07 08:22:30 +0200

Seen: 27 times

Last updated: Aug 07 '18