Forum Discussion
creating a list showing differences in row numbers for each integer in a database
I have a set of Excel databases in which each row contains a number from 1 to up to 500. Certain numbers are repeated in later rows, for example the integer "1" may be found in rows 1, 15, 32, 48, 97,....., and the integer "2" may be found in rows 2, 12, 27, 33, 54, 66,.... Some of my databases contain 10,000 or more rows. I would like to create a formula that could extract from each Excel database the distance, in row number (i.e. row number of the 2nd appearance minus row number of the first appearance), between each presence of a given integer. In the above examples, the results would be, for integer "1": 14, 17, 16, 49, and for integer "2": 10, 15, 6, 21, 12. I then want to show these results graphically, listing the results (distance in row number between 2 appearances of a given integer) on the x axis, and the number of time a given result occurs on the y axis.
Hi Nick, I have completed the first part of your task. Now I'm sure you can do your graphic on your own. I didn't understand how it should look.
The formula that generates your data could look like this:
=IFERROR(LET(data,$A$1:$A$99,integer,H$2,diffs,FILTER(ROW(data),data=integer),INDEX(diffs,SEQUENCE(COUNT(diffs)-1,,2))-INDEX(diffs,SEQUENCE(COUNT(diffs)-1,,1))),"")See also the enclosed example file.
4 Replies
Hi Nick, I have completed the first part of your task. Now I'm sure you can do your graphic on your own. I didn't understand how it should look.
The formula that generates your data could look like this:
=IFERROR(LET(data,$A$1:$A$99,integer,H$2,diffs,FILTER(ROW(data),data=integer),INDEX(diffs,SEQUENCE(COUNT(diffs)-1,,2))-INDEX(diffs,SEQUENCE(COUNT(diffs)-1,,1))),"")See also the enclosed example file.
dscheikey Thanks, you really know Excel formulas!! I will try it out on some of my database files and get back to you when I am sure it works, but I see with your example, which is very useful, how it works and the logic behind it. This is a great help for me, I will certainly give you credit if and when I publish work involving these databases. For your information, I am studying birdsong, and the "integers" represent specific phrase types sung by a vireo, which can be repeated at different intervals during a song bout. These song bouts can last 30 min or more, with about 1 phrase sung per second. I want to be able to represent the pattern of the occurence of distinct phrase types during such a song bout.
Assuming data=Sheet11:$A:$A entire spill could be generated as
=LET( ints, UNIQUE(TOROW(data, 3), 1), diff, LAMBDA(range,v, LET( pos, TOCOL(ROW(range) / (range = v), 3), DROP(pos, 1) - DROP(pos, -1) ) ), VSTACK( ints, IFNA( DROP( REDUCE("", ints, LAMBDA(a,v, HSTACK(a, diff(data, v)))), , 1 ), "" ) ) )
