Dec 04 2021 04:00 PM
@Josh_Waldner you can't do that but in the attached I created 2 alternatives using conditional formatting. In 1 option you add the cell coordinates in your lists and the corresponding cells in the grid will get colored. In the other option you type a letter like "b" for blue or "o" for orange into the grid which will cause that cell to get colored that color (and the letter is hidden) and then the list is created below based on that. (The formulas I used are based on Excel 365)
Dec 06 2021 06:48 AM
@mtarler i tried revising your formula on sheet A, but it does not seem to work
=IFS(ROW()-4<=AI5,
LET(b,IF(B2:X10="b",COLUMN(B2:X10)-1,"")&IF(B2:X10="b",INDEX(A1:A10,ROW(B2:X10)),""),
r,ROWS(b),
c,COLUMNS(b),
s,SEQUENCE(r*c,1,0),
list,INDEX(b,INT(s/c)+1,MOD(s,c)+1),
SORT(FILTER(list,list>""))),
ROW()-4<=(AI5+AI6),
LET(o,IF(B2:X10="o",COLUMN(B2:X10)-1,"")&IF(B2:X10="o",INDEX(A1:A10,ROW(B2:X10)),""),
r,ROWS(o),
c,COLUMNS(o),
s,SEQUENCE(r*c,1,0),
list,INDEX(o,INT(s/c)+1,MOD(s,c)+1),
SORT(FILTER(list,list>""))))
is it truly not possible? If not i will write some VBA that will work, but it will have to be very long.
i would like to get those two lists in one consecutive list.
Dec 06 2021 07:11 AM
I updated the formula to make 1 consecutive list but then it is harder (not impossible) to color those cells the corresponding color. In the attached I created the combined formula but didn't do the conditional formatting. There are a few ways to do it.
a) you could augment the actual cell text to make it easy (e.g. (instead of 1A, 2A, ... it could be (b)1A, (b)2A, ... or something more subtle like 1A. , 2A. , )
b) you could use helper cells like you added to tell conditional formatting when to switch from blue to orange.
Dec 06 2021 07:14 AM
Dec 06 2021 01:58 PM
For the conditional formatting I'd add some non-printable character at the end and format based on it. Like
=LET(
rTitle, $A2:$A10,
cTitle, $B$11:$X$11,
data, $B$2:$X$10,
rws, ROWS( data ),
cls, COLUMNS( data ),
k, SEQUENCE( rws * cls ),
nRow, INT( (k-1)/cls )+1, nCol, MOD( k-1, cls )+1,
blue, UNICHAR( 160 ),
orange, " ",
getBlue,
LET( str, "b",
check, --(data = str),
all,
IF( check = 0, "", INDEX( cTitle, SEQUENCE(, cls ) * check ) ) &
IF( check = 0, "", INDEX( rTitle, SEQUENCE( rws ) * check ) ),
lst, INDEX( all, nRow, nCol),
FILTER( lst, lst <> "" ) & blue
),
getOrange,
LET( str, "o",
check, --(data = str),
all,
IF( check = 0, "", INDEX( cTitle, SEQUENCE(, cls ) * check ) ) &
IF( check = 0, "", INDEX( rTitle, SEQUENCE( rws ) * check ) ),
lst, INDEX( all, nRow, nCol),
FILTER( lst, lst <> "" ) & orange
),
stack,
LET(
nB, ROWS( getBlue ),
nO, ROWS( getOrange ),
k, SEQUENCE( nB + nO),
IF( k <= nB, INDEX( getBlue, k ), INDEX( getOrange, k - nB ) ) ),
SORT( stack )
)
Dec 06 2021 02:14 PM
Dec 06 2021 03:49 PM
@Josh_Waldner So here is a much more simple and more efficient version and will work on any number of 'color codes'. The only thing is it will sort based on the color letter (i.e. "b" then "g" then "o" then "y") so pick your colors/color codes accordingly.
=LET(data,B2:X10,r,ROWS(data),c,COLUMNS(data),s,SEQUENCE(r*c,1,0),rw,INT(s/c)+1,cw,MOD(s,c)+1,
flatData,SORT(IF(s+1,LEFT(INDEX(data,rw,cw)&"_",1)&INDEX(A2:A10,rw)&INDEX(B11:X11,cw))),
ordered,FILTER(flatData,LEFT(flatData,1)<>"_",""),
MID(ordered,2,99))