hope the title wasn't too confusing.
this is my project:
I'm trying to figure out how to get the names on the right (NICK-GL) to stack on top of each other at the bottom, when one of the mon-fri boxes has one of the correlating names in it, i.e., when Nick is is displayed in F3, NICK-GL appears in F21. The names on the right will change from week to week. I'd like them to automatically bump to the top of the list at the bottom but stay in the order they are on the Name list.
If you already couldn't tell, I'm a beginner.
I found this formula on stackoverflow, but I don't know how to implement it into my sheet.
=LET(datal,E74:E83,datar,T74:T82,dell,"/",delr,"-",
dl,FILTER(datal,LEN(datal),""),
IFNA(XLOOKUP(TEXTBEFORE(dl,dell),
TEXTBEFORE(datar,delr),datar),dl))
If someone could also explain to me what each part of that means, I would tremendously appreciate it.
Well, if I have understood correctly, the following formula should work as per the given conditions:
• Formula used in cell C21
=LET(_Data, C3:C19,_Names, $J3:$J7,FILTER(_Names,1-ISNA(XMATCH(TEXTBEFORE(_Names,"-"),_Data)),""))
LET()
function, which helps in eliminating redundant calculations, avoids using repeated formulas/ranges and improves working functionalities of Excel by performance._Data
variable is the source range, in the below example it starts from C3:C19
(It might be different in your data, needs to suit as per your data, also don't use merged cells, instead use center across selection if its needed for formatting or designing, still its better to avoid, and use single cell only)._Names
variable is the range on which the following function will be using as lookup_value
and return the same one which matched as an output. J3:J7
(again change range as per your suit).TEXTBEFORE()
function extracting the names so that it can be used as a lookup_value
to match with the original source and return the positions of the respective names as per the source data.XMATCH()
to return the positions as mentioned above, and when not matched it will return as #N/A
hence instead of using ISNUMBER()
which can also be used, but used ISNA()
which returns TRUE
for errors while for numbers returns as FALSE
reversing them by using 1-
FILTER()
function now returning the output for those which are only TRUE
values. and when there is nothing matched or if there is no data it will use the last param of the FILTER()
and return as empty ""
(in screenshot its not shown, formula shows that)There is another shorter way of doing this is taking the advantage of COUNTIFS()
or COUNTIF()
function:
=LET(
_Data, C3:C19,
_Names, $J3:$J7,
FILTER(_Names,COUNTIF(_Data,TEXTBEFORE(_Names,"-"))))
Using One Single Dynamic Array Formula to return the output:
=LET(
_Data, C3:I19,
_Name, J3:J7,
_Rows, ROWS(_Name),
_Colums, COLUMNS(_Data),
IFERROR(MAKEARRAY(_Rows,_Colums,LAMBDA(r,c,
INDEX(FILTER(_Name,1-ISNA(XMATCH(TEXTBEFORE(_Name,"-"),
INDEX(_Data,,c))),""),r))),""))