Forum Discussion

Josh_Waldner's avatar
Josh_Waldner
Brass Contributor
Dec 04, 2021

index

Hello, everyone I would like to find a way to index rows and columns depending on the color of the cells. place quantity of shaded cells may vary in the grid space. see the attached for more detail...
excel
Josh_Waldner's avatar
Josh_Waldner
Brass Contributor
Dec 06, 2021

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 06, 2021

Josh_Waldner 

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 )

)
  • Josh_Waldner's avatar
    Josh_Waldner
    Brass Contributor
    Dec 06, 2021
    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.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Dec 06, 2021

      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))

       

Resources