Generate two columns based on a branching map of values

Starting with a flowchart:
Animals Flow.png

I've entered data into excel like so as a starting point:
Animals Excel.png

I'm using the "|" and "backslash" (it won't let me enter the actual character) symbols both as a visual aid for humans, and I'm also thinking I can search for those characters algorithmically. I don't NEED those characters, I'm just thinking it will be easier to do what I'm trying to do if they're there. What I'm trying to do is take this map, or any arbitrary map of any complexity, and generate a pair of columns of the relationship between the items. In this example, the list of relationships would be this:

animals relationship.png

I've been poking at this with a bunch of IFs and the various LOOKUP functions, but I feel like I'm getting nowhere, so I thought I'd see if the forum had any better ideas, because you usually do.

Please, upload a sample excel file with expected results.

So you would manually enter what you're showing in rows 1 -14, and expect the results what you're showing in rows 16-36?

What about going the other way - entering your data in rows 16-36 and generating rows 1-14?

As windknife said, please attach a sample file that we can work with.

I've made some progress. I've been able to (mostly) generate the second column. I highlighted the successful part in green. Columns Worksheet.xlsx

I used this formula:

=LET(range,K1:S14,FILTER(TOCOL(range,0),TOCOL(range,0)<>""))

Still working on the first column though, and revision to the second column...

Originally Posted by Gregb11

What about going the other way - entering your data in rows 16-36 and generating rows 1-14?

Going the other way doesn't work in the workflow this will be used for. The user is building the map. That is, the map doesn't exist yet, and they are adding entries. The formula is meant to collect the relationships the user has created so they have a list of relationships that need further processing with formulae I haven't gotten to yet.

Originally Posted by windknife

How about this?

I should have grown to expect this by now, but every time I see it, I'm still amazed. I don't know if this works yet. Mostly because I don't understand what you did, and it will take me time to analyze and test it. But you definitely approached this from a direction I didn't see coming. I thought I was being clever by using the LET and TCOL functions. I don't even know what most of the functions you used are. As I said, I'll have to analyze and test. But...wow.

Thank you. I'll reply again when I have feedback.

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Originally Posted by windknife

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Still working on it. This is going to take some time...

For archival reasons, I'm posting supplemental info here. The formula above had a weakness. It required the entire array it was looking at to be filled with data. I started another thread to seek a solution to the problem here. This resulted in a modification to the formula above as follows:

=LET(
  r,A1:N24,  
  a,INDEX(r,MIN(IF(r<>"",ROW(r))),MIN(IF(r<>"",COLUMN(r)))):INDEX(r,MAX(IF(r<>"",ROW(r))),MAX(IF(r<>"",COLUMN(r)))),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IF(ISODD(y),INDEX(a,y,),SCAN(,INDEX(a,y,),LAMBDA(m,n,IF(n=0,m,n))))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)-2),LAMBDA(x,y,IF(ISODD(y),x,VSTACK(x,DROP(REDUCE("",SEQUENCE(COLUMNS(b)),LAMBDA(m,n,IF(INDEX(b,y+1,n)<>0,VSTACK(m,INDEX(b,y,n),INDEX(b,y+2,n)),m))),1))))),1),
  WRAPROWS(c,2)
)

Still working on the "bigger picture" of this code.

I've run into a snag.

If rows are added above, the formula no longer works. I know this code is looking at odd and even rows, so I made sure the data was on even rows, but still, it stops functioning. This behavior also arises if the entire data entry area and formula are duplicated. I'm attaching the spreadsheet with a working implementation at the top, and a non working implementation if you scroll down.

Columns Worksheet 3.xlsx

amended formula,

=LET(
  r,A61:Z88,
  a,INDEX(A:ZZ,MIN(IF(r<>"",ROW(r))),MIN(IF(r<>"",COLUMN(r)))):INDEX(A:ZZ,MAX(IF(r<>"",ROW(r))),MAX(IF(r<>"",COLUMN(r)))),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IF(ISODD(y),INDEX(a,y,),SCAN(,INDEX(a,y,),LAMBDA(m,n,IF(n=0,m,n))))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)-2),LAMBDA(x,y,IF(ISODD(y),x,VSTACK(x,DROP(REDUCE("",SEQUENCE(COLUMNS(b)),LAMBDA(m,n,IF(INDEX(b,y+1,n)<>0,VSTACK(m,INDEX(b,y,n),INDEX(b,y+2,n)),m))),1))))),1),
  WRAPROWS(c,2)
)

That works. Thank you, and thank you for doing it so quickly. But I understand the formula even less now, lol.

You can use the following methods to see the results of each step.
a

=LET(
  r,A61:Z88,
  a,INDEX(A:ZZ,MIN(IF(r<>"",ROW(r))),MIN(IF(r<>"",COLUMN(r)))):INDEX(A:ZZ,MAX(IF(r<>"",ROW(r))),MAX(IF(r<>"",COLUMN(r)))),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IF(ISODD(y),INDEX(a,y,),SCAN(,INDEX(a,y,),LAMBDA(m,n,IF(n=0,m,n))))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)-2),LAMBDA(x,y,IF(ISODD(y),x,VSTACK(x,DROP(REDUCE("",SEQUENCE(COLUMNS(b)),LAMBDA(m,n,IF(INDEX(b,y+1,n)<>0,VSTACK(m,INDEX(b,y,n),INDEX(b,y+2,n)),m))),1))))),1),
  d,WRAPROWS(c,2),
  a
)

b

=LET(
  r,A61:Z88,
  a,INDEX(A:ZZ,MIN(IF(r<>"",ROW(r))),MIN(IF(r<>"",COLUMN(r)))):INDEX(A:ZZ,MAX(IF(r<>"",ROW(r))),MAX(IF(r<>"",COLUMN(r)))),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IF(ISODD(y),INDEX(a,y,),SCAN(,INDEX(a,y,),LAMBDA(m,n,IF(n=0,m,n))))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)-2),LAMBDA(x,y,IF(ISODD(y),x,VSTACK(x,DROP(REDUCE("",SEQUENCE(COLUMNS(b)),LAMBDA(m,n,IF(INDEX(b,y+1,n)<>0,VSTACK(m,INDEX(b,y,n),INDEX(b,y+2,n)),m))),1))))),1),
  d,WRAPROWS(c,2),
  b
)

c

=LET(
  r,A61:Z88,
  a,INDEX(A:ZZ,MIN(IF(r<>"",ROW(r))),MIN(IF(r<>"",COLUMN(r)))):INDEX(A:ZZ,MAX(IF(r<>"",ROW(r))),MAX(IF(r<>"",COLUMN(r)))),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IF(ISODD(y),INDEX(a,y,),SCAN(,INDEX(a,y,),LAMBDA(m,n,IF(n=0,m,n))))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)-2),LAMBDA(x,y,IF(ISODD(y),x,VSTACK(x,DROP(REDUCE("",SEQUENCE(COLUMNS(b)),LAMBDA(m,n,IF(INDEX(b,y+1,n)<>0,VSTACK(m,INDEX(b,y,n),INDEX(b,y+2,n)),m))),1))))),1),
  d,WRAPROWS(c,2),
  c
)

d

=LET(
  r,A61:Z88,
  a,INDEX(A:ZZ,MIN(IF(r<>"",ROW(r))),MIN(IF(r<>"",COLUMN(r)))):INDEX(A:ZZ,MAX(IF(r<>"",ROW(r))),MAX(IF(r<>"",COLUMN(r)))),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,IF(ISODD(y),INDEX(a,y,),SCAN(,INDEX(a,y,),LAMBDA(m,n,IF(n=0,m,n))))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)-2),LAMBDA(x,y,IF(ISODD(y),x,VSTACK(x,DROP(REDUCE("",SEQUENCE(COLUMNS(b)),LAMBDA(m,n,IF(INDEX(b,y+1,n)<>0,VSTACK(m,INDEX(b,y,n),INDEX(b,y+2,n)),m))),1))))),1),
  d,WRAPROWS(c,2),
  d
)

I'm being pulled onto a different job by my task masters, so my work on this will have to pause for a time. I'm going to mark this thread as solved, but may come back with some comments about the new formulae by windknife and djclements.