Forum Discussion
index
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)
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.
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 ) )- SergeiBaklan
I'm sorry, but i dont care as much about the conditional formatting part to color the cells. and I would like to read first the blue bottom to top, left to right, then the orange, etc.
mtarler
I like your formula better, but i am having a bit trouble adding 3 additional lists to your formula. could you rewrite your "out" parameter to include 5 different lists? i can get the rest from there. there is blue, orange, green purple, and gray.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))
- FYI the reason your formula didn't work is that ROW() returns the row number of the cell the formula is in. It doesn't know anything about all the other cells that are potentially being returned. Therefore it the if statement is true and only returns the first condition (the blue list). In my combined solution I create a 'dummy' sequence as long as the combined total number of rows so that the IFS function is forced to act all those rows.
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.
