Forum Discussion
Solved
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...
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.
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
),
""
)
)
)
Thanks Sergei, interesting that two such different formulas can be applied, Excel language is a mystery to me. I am currently using dscheikey's formula on my data and it is working. Making graphs of the results is something I was able to do with my few Excel tools!