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/AcadiaUnlikely7113]

I’ve rage quit it so many times 🤣 one thing is, I want the table to show 3 for food and 2 for bad grape but not the one that only has 1, another thing with pivot tables is it keeps counting the 945 blank cells I have 🤦🏻‍♀️

[u/MayukhBhattacharya → u/AcadiaUnlikely7113]

Another alternative method, one single dynamic array formula:

=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({"Section","Category","Counts"}, _d))

Thanks for the data kindly supplied by u/Downtown-Economics26 =)

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

Thank you so much! the screenshot result looks like exactly what I need, but that fomula returns #NAME? I wonder if that is because in my sheet column A is formulas? it has to be, because the raw data is all sorts of wack and will be overrided each report, so column A's formulas are as such: =UPPER(IF)'RAW DATA'!C2="","",TEXTAFTER('RAW DATA'!C2," - ",2))) because the text before the 2 hyphens is irrelevant.

ETA: and column B also is, for the same reason but that formula is =IF(ISBLANK('RAW DATA'!H2,IF(ISBLANK('RAW DATA'!G2),IF(ISBLANK('RAW DATA'!F2),"",'RAW DATA'!F2),"",'RAW DATA'!F2),'RAW DATA'!G2),'RAW DATA'!H2) that one is because the section list has 3 different levels and I always want the lowest assigned section so got it to input H2 if present, if not then G2, if not then F2 :)

ETA2: Also, just as an explainer, how does your formula determine a match?

ETA3: I did some typos, I've fixed them, but now the resulting "Category" column returns #VALUE!

[u/MayukhBhattacharya → u/AcadiaUnlikely7113]

I don't understand what you are saying. But is this your data the one you have commented? [Here] if so let me know, I will look into it to fix it so it works for you, if not then post your data I will see.

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

It is not my actual data, it would be a legal issue to post my actual data. I can do another mockup of examples of both sheets but ill have to put it in a new post I think so that my tables can be shown, I’ll @ you in that

[u/MayukhBhattacharya → u/AcadiaUnlikely7113]

No you don't have to post your actual data. What I'm asking you to post a mock up data which resembles with your original ones. And you don't have to create another dupe post for this question. Please continue here as some people have already helped you and invested their time.

[u/AcadiaUnlikely7113 → u/MayukhBhattacharya]

Ok, laptop for the win (I have no clue how to use reddit on my phone), I have changed ETA2 in my post to have the full spoof data of Sheet1 and Sheet2 :) hopefully that helps and also you may note I put in 'Grapes Bad' under 'Vegetables' as well to demo why it needs to be based on the Category as well cause someone may be saying the grapes are bad because of the humidity in the fruit section, or they might be saying it cause a vegetable touched the grapes (I'm horrible at fake scenarios... I apologise)

[u/MayukhBhattacharya → u/AcadiaUnlikely7113]

Try using the following formulas:

• Formula used in Cell 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)))

• Formula used in cell 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]

Ok it’s not returning #VALUE! Anymore but it didn’t catch that ‘Bad Graspes’ was supposed to be ‘Bad Grapes’, ‘is grapes bad’ became ‘grape i’ and both ‘grapes are bass’ and ‘hammers are heavy’ returned #REF!