Feb 29 2024 07:07 AM
Hi there!
little background:
I have two sheets: "Sheet1" and "Keywords", the "Keywords" sheet has two keywords columns ( H & G ) and a category column ( D ), and "Sheet1" has a description column (Column E).
Requirement:
The description cell should contain keywords from both keyword columns ( at least one from H and one from G), if it does, then the formula should return the category ( D ), if it doesn't however, then it should check it against the next row in the keywords column and so on.
What I have done:
```
=IF(
C2="Incident",
IFS(
AND(OR(COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$G$213,,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$G$213,,"،") & "*")), OR(
COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$H$213,, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$H$213,, "،") & "*"))),
Keywords!$D$213,
AND(OR(COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$G$214,,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$G$214,,"،") & "*")), OR(
COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$H$214,, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$H$214,, "،") & "*"))),
Keywords!$D$214,
.....
.....
.....
AND(OR(COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$G$300,,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$G$300,,"،") & "*")), OR(
COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$H$300,, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$H$300,, "،") & "*"))),
Keywords!$D$300
),
"not incident"
)
```
The problem with this code:
this code works perfectly, but the issue is that excel limits me to ~8000 characters, so I can't go through the whole keywords columns.
Is there more efficient way of doing this? a simple FOR loop would suffice but excel doesn't support them afaik.
Your help is much appreciated!
Feb 29 2024 08:58 AM
Solution=TOROW(IFERROR(FILTER(Keywords!$D$213:$D$300,
DROP(N(
REDUCE("",SEQUENCE(88),
LAMBDA(u,v,VSTACK(u,IF(C2="Incident",
AND(
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*")),
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"))))))))
,1)),
"not incident"))
This should work if i correctly understand what you want to do. The formula is in cell H2 in the attached sample file and filled down. If there are two or more matches for the search criteria in columns E and G of Sheet1 the formula spills the results to the right.
Feb 29 2024 09:10 AM - edited Feb 29 2024 09:11 AM
I'm guessing you'd want to pull multiple categories where applicable. Also, I've included a 'delim' parameter so you can add or remove those as needed.
Try this:
=LET(
delim, {" ", ","},
GetCategory, LAMBDA(string,
LET(
words, TEXTSPLIT(string, delim),
c, COUNTA(words),
check_g, N(words = Key_1),
check_h, N(words = Key_2),
m, SEQUENCE(c, , 1, 0),
vector, MMULT(check_g, m) + MMULT(check_h, m),
categories, FILTER(Category, vector = 2, "Not Found"),
TEXTJOIN(", ", , categories)
)
),
BYROW(Description, GetCategory)
)
Feb 29 2024 09:25 AM
Feb 29 2024 09:27 AM
Feb 29 2024 08:58 AM
Solution=TOROW(IFERROR(FILTER(Keywords!$D$213:$D$300,
DROP(N(
REDUCE("",SEQUENCE(88),
LAMBDA(u,v,VSTACK(u,IF(C2="Incident",
AND(
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*")),
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"))))))))
,1)),
"not incident"))
This should work if i correctly understand what you want to do. The formula is in cell H2 in the attached sample file and filled down. If there are two or more matches for the search criteria in columns E and G of Sheet1 the formula spills the results to the right.