Forum Discussion
excel search for characters not listed in rows above
In this worksheet, the stories (column D) are only allowed to use letters that have already been learned (column B). The story does not need to include all of the previous letters. The story may not use letters that have not yet been taught. When I type a story into column D, I would like column E to tell me if the story contains letters that are not listed in rows above. Can you help me? Thanks!
intliteracylinguist so that original example/sheet image was showing merged cells which cause lots of problems. Also having varying number of rows per group causes a bit of complexity. The following formula works but is extra complicated because of that and assumes a max of 10 rows for the last added letter group:
=LET(letters,$B$2:INDEX(B:B,IFERROR(XMATCH(A2+1,A:A),ROW(A2)+10)-1), IF(TRIM(CONCAT(TEXTSPLIT(D2,FILTER(letters,LEN(letters)>0,""),,FALSE,1)))="", "Valid", "InValid"))in the attached is both merged table and unmerged. In the unmerged you can more easily copy and paste the formula down but you can't on the merged
5 Replies
=IF(SUM(N((ISNUMBER(SEARCH(B5:$B$12,D4))))),"invalid","")An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In cell B12 is a search value which isn't a letter and therefore doesn't appear in a story in column D.
Unmerge the cells in column E.
Enter the following formula in E2:
=IF(A2="", "", LET(sentence, SUBSTITUTE(D2, " ", ""), length, LEN(sentence), used, MID(sentence, SEQUENCE(length), 1), last, IFERROR(MATCH(A2+1, A:A, 0)-1, 10000), colB, INDIRECT("B2:B"&last), letters, CONCAT(FILTER(colB, colB <>0)), valid, AND(ISNUMBER(SEARCH(used, letters))), IF(valid, "", "Invalid")))
Fill down.
I'm getting 'Invalid' for all rows with story text. In this example, only D14 is invalid.
intliteracylinguist so that original example/sheet image was showing merged cells which cause lots of problems. Also having varying number of rows per group causes a bit of complexity. The following formula works but is extra complicated because of that and assumes a max of 10 rows for the last added letter group:
=LET(letters,$B$2:INDEX(B:B,IFERROR(XMATCH(A2+1,A:A),ROW(A2)+10)-1), IF(TRIM(CONCAT(TEXTSPLIT(D2,FILTER(letters,LEN(letters)>0,""),,FALSE,1)))="", "Valid", "InValid"))in the attached is both merged table and unmerged. In the unmerged you can more easily copy and paste the formula down but you can't on the merged
