I am trying to replace the "interacted with you!" text in my formula with the text in E$2:E$24 where M$2:M$24=$B2, and to only include this text when there is a cell where $B2=M$2:M$24
Essentially, I want the text for Player1 T0Concatenate to say
@Player1
You have woken up from a drunken stupor, you have 10 :coin: and :green_square::green_square::green_square::green_square::green_square::green_square::green_square::green_square::green_square::green_square: hp. You belong to no team and are located at the **Tavern**.
In your area you :eye:
@Player1
@Player2
@Player3
@Player4
@Player5
@Player6
@Player7
@Player8
@Player9
@Player10
@Player11
@Player12
@Player13
@Player14
@Player15
@Player16
@Player17
@Player18
@Player19
@Player20
@Player21
@Player22
@Player23
You were targeted! :dart:
@Player2 used Light attack on you!
@Player3 used Light attack on you!
My current formula returns:
@Player1
You have woken up from a drunken stupor, you have 10 :coin: and :green_square::green_square::green_square::green_square::green_square::green_square::green_square::green_square::green_square::green_square: hp. You belong to no team and are located at the **Tavern**.
In your area you :eye:
@Player1
@Player2
@Player3
@Player4
@Player5
@Player6
@Player7
@Player8
@Player9
@Player10
@Player11
@Player12
@Player13
@Player14
@Player15
@Player16
@Player17
@Player18
@Player19
@Player20
@Player21
@Player22
@Player23
@Player2 interacted with you!
@Player3 interacted with you!
I feel like I have just been staring at my problem for too long! Does anyone know a way to solve this?
Currently, I am using the formula
=Concatenate(char(10),"@",B2,char(10)," You ",E2,", you have ",G2," :coin: and ",J2," hp.",IF(L2=""," You belong to no team",CONCATENATE(" You belong to team ",L2))," and are located at the **",K2,"**.",IF(H2="","",Concatenate(char(10),char(10),"In your :school_satchel: you find: ",char(10),H2)),char(10),char(10),"In your area you :eye: ",char(10),"@",ARRAYFORMULA(TEXTJOIN(concatenate(char(10),"@"),TRUE,IF(K$2:K$24=K2,B$2:B$24))),char(10),char(10),"@",ARRAYFORMULA(TEXTJOIN(concatenate(" interacted with you!",char(10),"@"),TRUE,IF(M$2:M$24=$B2,B$2:B$24,"")))," interacted with you!",char(10))
CodePudding user response：
try in row 1:
=ARRAYFORMULA({"T0Concatenate"; TRIM(SUBSTITUTE(IF(B2:B="",,"@"&B2:B&"×You "&E2:E&", you have "&G2:G&" :coin: and "&J2:J&" hp. You belong to "&
IF(L2:L="", "no ", )&"team"&IF(L2:L="",, " "&L2:L)&" and are located at the **"&K2:K&"**.××"&
IF(H2:H="",,"In your :school_satchel: you find: ×"&H2:H&"××")&
"In your area you :eye:"®EXREPLACE(IFNA(VLOOKUP(K2:K, TRIM(SPLIT(FLATTEN(QUERY(QUERY({ROW(B2:B), "×@"&B2:B, K2:K&"¤"},
"select max(Col2) where not Col3 starts with '¤' group by Col1 pivot Col3"),,9^9)), "¤")), 2, 0)), " ?×@\b"&B2:B&"\b ?", )&"××"&
IFNA("You were targeted! :dart:"&VLOOKUP(B2:B, TRIM(SPLIT(FLATTEN(QUERY(QUERY({ROW(B2:B), "×@"&B2:B&" "&VLOOKUP(B2:B&"/"&LEFT(D2:D24,1), Stats!A2:D, 2, 0)&" on you!", M2:M&"¤"},
"select max(Col2) where not Col3 starts with '¤' group by Col1 pivot Col3"),,9^9)), "¤")), 2, 0))&
IFNA(VLOOKUP(Turns!B2:B, TRIM(SPLIT(FLATTEN(QUERY(QUERY({ROW(Turns!B2:B), "×@"&Turns!M2:M&" interacted with you!", Turns!B2:B&"¤"},
"select max(Col2) where not Col3 starts with '¤' and not Col2 contains '×@ interacted' group by Col1 pivot Col3"),,9^9)), "¤")), 2, 0))), "×", CHAR(10)))})
