Forum Discussion
Searching column cells for similar text values and summarising in another column
so I have a couple of thoughts. First off the example was very simple in the sense that each output was basically based on a key word. So you could use:
=COUNTIFS($B$2:$B$10,"*fresh*")
to count all items with "fresh" somewhere in the topic. you could create a column next to your output table with a list of keywords so then you could use:
=COUNTIFS($B$2:$B$10,"*"&D2&"*")
where D2 is the keyword "fresh" or "freshness" or whatever you want
you can even make it an array output using:
=COUNTIFS($B$2:$B$10,"*"&D2:D4&"*")
you could get more fancy and have a list of possible words and use something like:
=LET(list,TEXTSPLIT(C2," "),SUM(--(MMULT(--ISNUMBER(SEARCH(list,$B$2:$B$10)),SEQUENCE(COLUMNS(list),,1,0))>0)))
where this will break each word in C2 up and check if it exists in each row of the inputs and then OR all those together to find how many rows have at least 1 of those key words. In your example this procudes a wrong result for the 3rd row as "issues" is found in another line.
there are MANY other options and roads you could go down with excel functions and could simulate your macro solution very closely if not the same.
That all said another option is to add the Excel Labs plug-in and use the Labs GenerativeAI plugin and let an AI service help determine the results:
=LABS.GENERATIVEAI("does """&B2&""" have to do with """&H1&"""?")
this requires you to have an AI account and give the plug-in an access key
Hi m_tarler
Thanks again for all your guidance on the above.
The code has been working well but I now need to add an IF condition to the formula.
I tried changing the the SUM to SUMIF but it doesn't work.
I need to perform the search in $B$2:$B$10 only IF the date in $C$2:$C$10 matches a month defined in F1
Are you able to advise where this condition could be added to the formula?
My other question would be, is it possible with another formula to actually list those rows matching the count?
Ok so it would seem the prior solution was:
=LET(list,TEXTSPLIT(C2," "),SUM(--(MMULT(--ISNUMBER(SEARCH(" "&list&" "," "&$B$2:$B$10&" ")),SEQUENCE(COLUMNS(list),,1,0))>0)))
so lets build on that:
=LET(list,TEXTSPLIT(C2," "), data, $B$2:$B$10, dates, $C$2:$C$10, month, $F$1, fdata, FILTER(data, (MMULT(--ISNUMBER(SEARCH(" "&list&" "," "&data&" ")),SEQUENCE(COLUMNS(list),,1,0))>0) * (dates> EOMONTH(month,-1) ) * (dates <= EOMONTH(month,0) ), "none found") IF( TAKE(fdata,1)="none found" , 0 , ROWS(fdata) ) )so on row 6 is the same check used before but instead of doing a 'sum' of the 0's and 1's it produces, I use it as a filter and multiply it by line 7 which is a filter for the date range (assuming F1 is an actual date in the month of interest).
Line 9 wil then output how many rows were found
To output the actual rows just change row 9 to "fdata" to return that filtered range.
hope that makes sense and works
m_tarlerThanks again for your advice as always.
I tried your latest formula and I keep getting a "too few arguments for this function" error. I cant see where the error might be. Any ideas?
sorry, i accidentally dropped a comma at the end of line 8
also that formula has the LIST of terms coming from C2 and the DATES in columnn C so they need to point to the correct locations.
If you attach a sample book it is usually easier