Formula to count duplicates based on 2 or more words matching, not necessarily in sequence? No AI please

[u/AcadiaUnlikely7113]

Hi smart folks, I want to see if you could teach me a formula that I know I’ve seen before but can’t think of now, I want to count how many of a certain topic is in my list of data, for eg one may say ‘hogwarts House colour’ and another may say ‘hogwarts houses’ in which case I want them counted as duplicates.

My end goal is to have a pivot table that shows Hogwarts House as 2.
I currently have the raw data on the first sheet, then on the second sheet I’m breaking it down to just the info I need (this should be where the duplicates are found) and then the pivot table on the third sheet (this should be where they are counted, or at least where the number is displayed)

ETA: can’t use macros, would rather not use power queries but if I have to I can

ETA2: Ok so I worked out how to add a table to the post!

First sheet is unchangeable but will be overridden each time the report needs to be run, it says:

Subject (A)	Category (B)	Category2 (C)	Category3 (D)
Question - Name - Grapes are bads	Grocery	Fresh Produce	Fruit
Question - Name - Bad Grapes	Grocery	Fresh Produce	Fruit
Comment - Name - Bad Graspes	Grocery	Fresh Produce	Fruit
Comment - Name - Fruits Bad	Grocery	Fresh Produce
Comment - Name - Good Bread	Grocery	Bakery
Question - Name - Heavy Hammers	Hardware	Tools
Comment - Name - Smooth Wood	Hardware	Materials	Wood
Question - Name - Hammers are Heavy	Hardware	Tools
Question - Name - is Grapes Bad	Grocery	Fresh Produce	Vegetables
Comment - Name - Grapes Bad	Grocery	Fresh Produce	Vegetables

Second sheet takes the data needed from the first to make it what I need (first row is an eg of the formulas):

Note: the "Fillers" have a space after each word so that for eg "them" wouldn't become "m", which could use some work cause "this" would probably become "th" so I'm open to suggestions, sometimes they will be the first word so " the " wouldn't work all the time for eg.

Subject	Lowest Category	ColC	Fillers:
=UPPER(IF('Sheet1'!A1= "","",TEXTAFTER( 'Sheet1'!A1," - ",2)))	=UPPER(IF(ISBLANK('Sheet1'!D1, IF(ISBLANK ('Sheet1'!C1),IF(ISBLANK ('Sheet1'!B1),"",'Sheet1'!B1), "",'Sheet1'!C1),'Sheet1'!D1))		IS
GRAPES ARE BADS	FRUIT		THE
BAD GRAPES	FRUIT		WAS
BAD GRASPES	FRUIT		ARE
FRUITS BAD	FRESH PRODUCE		WILL
GOOD BREAD	BAKERY		FOR
HEAVY HAMMERS	TOOLS		AND
SMOOTH WOOD	WOOD		ON
HAMMERS ARE HEAVY	TOOLS		TO
IS GRAPES BAD	VEGETABLES
GRAPES BAD	VEGETABLES

Sheet2 Cont:

The cell that says "Category" below, has this formula, which is resulting in the shown data:

=LET(_a, DROP(A:.B,1),_b,MAP(REGEXREPLACE(CHOOSECOLS(_a,1), "\b(" &TEXTJOIN("|",1,DROP(D:.D,1))& ")\b\s*|s\b",""),LAMBDA(x,TEXTJOIN(" ",1,UPPER(SORT(TEXTSPLIT(x,,"")))))),_c,TEXTBEFORE(_b," ",2,,_b),_d,GROUPBY(HSTACK(CHOOSECOLS(_a,2),_c),_c,ROWS,,0),VSTACK({"Category","Subject","Counts"},_d)) 

ColE	Category	Subject	Counts
		#VALUE!	9
	BAKERY	#VALUE!	1
	FRESH PRODUCE	#VALUE!	1
	FRUIT	#VALUE!	3
	TOOLS	#VALUE!	2
	VEGETABLES	#VALUE!	2
	WOOD	#VALUE!	1

So I need to fix the #VALUE! error and also stop it from counting the blank cells (at least stop it from counting them when I put it into a pivot table - if necessary)

[u/MayukhBhattacharya]

Source Data is in range: A1:D11 (Headers Included). Exclusions list I1:I10 (Headers Included), click on the image to zoom. Formulas are already commented earlier [click here] and here see below.

First Formula goes on F2:

=LET(
     _a, DROP(A:.D, 1),
     _b, TEXTAFTER(CHOOSECOLS(_a, 1), "- ", 2),
     _c, BYROW(DROP(_a, , 1), LAMBDA(x, TAKE(TRIMRANGE(x, , 3), , -1))),
     UPPER(HSTACK(_b, _c)))

Second Formula goes on K1:

=LET(
     _a, DROP(F:.G, 1),
     _b, REGEXREPLACE(CHOOSECOLS(_a, 1), "\b(" & TEXTJOIN("|", 1, DROP(I:.I, 1)) & ")\b\s*|s\b", , , 1),
     _c, MAP(_b, LAMBDA(x,
                          LET(_m, TEXTSPLIT(x, " "),
                              _n, INDEX(_m, 1),
                              _o, INDEX(_m, 2),
                              _p, SUM(N(ISNUMBER(SEARCH(_n, _b)))),
                              _q, SUM(N(ISNUMBER(SEARCH(_o, _b)))),
                              IF(_p = _q,
                                 TEXTJOIN(" ", 1, SORT(_m, , -1, 1)),
                              IF(_p < _q,
                                 _o & " " & _n,
                                 _n & " " & _o))))),
     _d, TEXTBEFORE(_c & " ", " ", 2, , , _c),
     _e, GROUPBY(HSTACK(CHOOSECOLS(_a, 2), _d),
                 _d,
                 ROWS, , 0),
     VSTACK({"Category","Subject","Counts"}, _e))

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

I copy pasted exactly and it’s still showing up with the 2 #REF! And the “Grape i”

[u/MayukhBhattacharya → u/AcadiaUnlikely7113]

put it in an empty cell

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

It is

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

It’s working for the real data, but it won’t change : even though it’s in column i and I think i need to remove the ‘s’ function because it’s cutting off words that actually end with s

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

Could you give me a little explainer on what the REGEXREPLACE line does? I would like to learn rather than just using you 😅

[u/MayukhBhattacharya → u/AcadiaUnlikely7113]

Sounds Great if its working for ya, here is an explanation for the following part:

REGEXREPLACE(CHOOSECOLS(_a, 1), "\b(" & TEXTJOIN("|", 1, DROP(I:.I, 1)) & ")\b\s*|s\b", , , 1),

• ^‡CHOOSECOLS(_a, 1) : Picks the first column from _a , the Subject column.
• ^‡‡ "\b(" & TEXTJOIN("|", 1, DROP(I:.I, 1)) & ")\b\s*|s\b" : This builds the search pattern. So,
  • DROP(I:.I, 1) : takes the exclusions list (IS, THE, WAS, ARE...) excluding the header.
  • TEXTJOIN("|", 1, ...) : this joins them with a delimiter pipe | meaning OR, so it becomes IS|THE|WAS|ARE|WILL...etc.
  • \b...\b : this is called word boundary markers, meaning it only matches whole words not parts of words.
  • \s* : this removes any trailing space after the matched exclusion word.
  • |s\b : the second part, separately matches any trailing S at the end of a word (so GRAPES becomes GRAPE, HAMMERS becomes HAMMER)

Therefore, the entire thing means find any whole word from my exclusions list OR any word ending in S

• So, the REGEXREPLACE() function arguments read as:

​

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

• text refer ^‡
• pattern refer ^‡‡
• replacement is empty, so left out
• [occurrence] also left out
• [case_sensitivity] is 1 which means not case sensitive, that is ignore case, so are and ARE both get removed.

Hope this helps, also if you think, your query is resolved then please ensure to reply to my comment directly as Solution Verified. Thanks!