Forum Discussion

Nick_1987's avatar
Nick_1987
Copper Contributor
Nov 26, 2022
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...
BI & Data Analysis
excel
office 365
  • dscheikey's avatar
    dscheikey
    Nov 26, 2022

    Nick_1987

    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's avatar
dscheikey
Bronze Contributor
Nov 26, 2022

Nick_1987

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.

Nick_1987's avatar
Nick_1987
Copper Contributor
Nov 26, 2022

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 27, 2022

    Nick_1987 

    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
            ),
            ""
        )
    )
)
    • Nick_1987's avatar
      Nick_1987
      Copper Contributor
      Nov 28, 2022
      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!

Resources