Forum Discussion
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...
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.
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))