I have a Matrix that has row names & header names that I am trying to convert into a list. Is there a formula that could do this? I have tried a few options but can't wrap my head around how to explain the problem. So please see the picture for clarity.
I am trying to take the matrix and convert to a list that I can script into the system.
=LET(table,A1:D5,
h,TAKE(table,1),
d,DROP(table,1),
srl,TAKE(d,,1),
scl,DROP(h,,1),
sv,DROP(d,,1),
drl,TOCOL(IF(SEQUENCE(,COLUMNS(scl)),srl)),
dcl,TOCOL(IF(SEQUENCE(ROWS(srl)),scl)),
dv,TOCOL(sv),
r,HSTACK(drl,dcl,dv),
r)
r with any other variable to see what it holds.
After removing the redundant variables h, sv, and r:
=LET(table,A1:D5,
d,DROP(table,1),
srl,TAKE(d,,1),
scl,DROP(TAKE(table,1),,1),
drl,TOCOL(IF(SEQUENCE(,COLUMNS(scl)),srl)),
dcl,TOCOL(IF(SEQUENCE(ROWS(srl)),scl)),
dv,TOCOL(DROP(d,,1)),
HSTACK(drl,dcl,dv))
Further removing the variables drl, dcl, and dv:
=LET(table,A1:D5,
d,DROP(table,1),
srl,TAKE(d,,1),
scl,DROP(TAKE(table,1),,1),
HSTACK(TOCOL(IF(SEQUENCE(,COLUMNS(scl)),srl)),
TOCOL(IF(SEQUENCE(ROWS(srl)),scl)),
TOCOL(DROP(d,,1))))
or the same:
=LET(
table,A1:D5,
d,DROP(table,1),
srl,TAKE(d,,1),
scl,DROP(TAKE(table,1),,1),
HSTACK(
TOCOL(IF(SEQUENCE(,COLUMNS(scl)),srl)),
TOCOL(IF(SEQUENCE(ROWS(srl)),scl)),
TOCOL(DROP(d,,1))
)
)
