Discussion Re: index in Excel
https://techcommunity.microsoft.com/t5/excel/index/m-p/3032052#M125021
<P><LI-USER uid="1226241"></LI-USER> </P>
<P>For the conditional formatting I'd add some non-printable character at the end and format based on it. Like</P>
<LI-CODE lang="excel-formula">=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 )
)</LI-CODE>Mon, 06 Dec 2021 21:58:45 GMTSergei Baklan2021-12-06T21:58:45Zindex
https://techcommunity.microsoft.com/t5/excel/index/m-p/3030641#M124799
<P>Hello, everyone</P><P>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.</P><P>see the attached for more details</P>Sat, 04 Dec 2021 20:51:38 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3030641#M124799Josh_Waldner2021-12-04T20:51:38ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3030676#M124810
<P><LI-USER uid="1226241"></LI-USER> 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)</P>Sun, 05 Dec 2021 00:00:26 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3030676#M124810mtarler2021-12-05T00:00:26ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3031475#M124931
<P><LI-USER uid="570951"></LI-USER> i tried revising your formula on sheet A, but it does not seem to work</P><LI-CODE lang="applescript">=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>""))))</LI-CODE><P>is it truly not possible? If not i will write some VBA that will work, but it will have to be very long.</P><P>i would like to get those two lists in one consecutive list.</P>Mon, 06 Dec 2021 14:48:30 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3031475#M124931Josh_Waldner2021-12-06T14:48:30ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3031497#M124939
<P><LI-USER uid="1226241"></LI-USER> </P><P>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. </P><P>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. , ) </P><P>b) you could use helper cells like you added to tell conditional formatting when to switch from blue to orange.</P>Mon, 06 Dec 2021 15:11:28 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3031497#M124939mtarler2021-12-06T15:11:28ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3031501#M124940
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.Mon, 06 Dec 2021 15:14:54 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3031501#M124940mtarler2021-12-06T15:14:54ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3032052#M125021
<P><LI-USER uid="1226241"></LI-USER> </P>
<P>For the conditional formatting I'd add some non-printable character at the end and format based on it. Like</P>
<LI-CODE lang="excel-formula">=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 )
)</LI-CODE>Mon, 06 Dec 2021 21:58:45 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3032052#M125021Sergei Baklan2021-12-06T21:58:45ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3032075#M125026
<LI-USER uid="521" login="Sergei Baklan"></LI-USER><BR />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.<BR /><LI-USER uid="570951" login="mtarler"></LI-USER><BR />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.Mon, 06 Dec 2021 22:14:45 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3032075#M125026Josh_Waldner2021-12-06T22:14:45ZRe: index
https://techcommunity.microsoft.com/t5/excel/index/m-p/3032197#M125040
<P><LI-USER uid="1226241"></LI-USER> 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.</P><LI-CODE lang="excel-formula">=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))</LI-CODE><P> </P>Mon, 06 Dec 2021 23:49:46 GMThttps://techcommunity.microsoft.com/t5/excel/index/m-p/3032197#M125040mtarler2021-12-06T23:49:46Z