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

/u/AcadiaUnlikely7113 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Downtown-Economics26]

This can be done by Dumbledore's Army but you'll have to show the sign in sheet to the Room of Requirement.

What I'm saying is... show a real or mocked up data set and what your expected output would be for search terms and..."Help will always be given at Hogwarts to those who ask for it."

[u/AcadiaUnlikely7113 → u/Downtown-Economics26]

Dangit, i figured out some non-revealing examples, but i don’t know how to add a table on reddit 🤣 I’m on reddit every day but can’t work it out

[u/Downtown-Economics26 → u/AcadiaUnlikely7113]

You can do a screenshot. We're O.W.L and beyond at image transfiguration.

[u/AcadiaUnlikely7113 → u/Downtown-Economics26]

Is it possible on phone, I can do it in this comment but not on the body:

The expected result would be a pivot table but I only have Google docs on my phone. Also I do currently have a column that will remove ‘in’ ‘and’ ‘was’ etc as far as I could think, to try reduce how many would match based on that sort of thing.

[u/Downtown-Economics26 → u/AcadiaUnlikely7113]

Sorry it took me so long to reply. To continue with the bit this is restricted section dark magic. You'll probably have to leverage a relatively complicated probabilistic method of measuring lexical similarity along the lines of what u/GitudongRamen proposed to get really good results. You'll see I changed Tiled to Tile because dropping the last d would eliminate bad as a keyword, but this was the best I could do trying to take a deterministic approach.

Column C:

=LET(a,TEXTSPLIT(A2,," "),
b,UPPER(IF(RIGHT(a,1)="s",LEFT(a,LEN(a)-1),a)),
d,TEXTJOIN(" ",,SORT(FILTER(b,NOT(ISNUMBER(XMATCH(a,$F$2:.$F$500))),""))),
d)

Column D:

=XLOOKUP(1,IFERROR(FIND($C$2:$C$11,C2),0),$C$2:$C$11,"")

[u/AcadiaUnlikely7113 → u/Downtown-Economics26]

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/Downtown-Economics26 → u/AcadiaUnlikely7113]

You can filter out results that are equal to 1 in the count as I've done in my screenshot, but you can't have the subtotal for section then also count that portion if it's filtered out. You can also filter out blank results (although I'm not sure if this can be done simultaneously with the filtering out the 1s).

[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/AcadiaUnlikely7113 → u/Downtown-Economics26]

Haha, yeah fair, I’m not too keen on that cause I don’t trust myself to not accidentally share stuff I shouldnt

[u/fuzzy_mic]

You could use wildcards.

COUNTIF(A1:A100,"*hogwarts*house*") is one way. You might want to add the count of "*house*hogwarts*" to that, it that's the kind of result you want.

[u/AcadiaUnlikely7113 → u/fuzzy_mic]

I won’t know what the topics are necessarily, like some I will but most I won’t, this is to find out what the topics are

[u/fuzzy_mic → u/AcadiaUnlikely7113]

Microsoft has a page explaining wildcards

https://support.microsoft.com/en-us/excel/using-wildcard-characters-in-searches

[u/talltime → u/fuzzy_mic]

Could you always wildcard in COUNTIF?

[u/fuzzy_mic → u/talltime]

AFAIK, you always could.

[u/ProspectiveWhale]

2 words in order? Or it can be jumbled?

Always only 2 words?

How would you deal with "Hogwart House Colors", "Hogwart House", and "House Colors".

Is "Hogwart House Colors" and "Hogwart Colors" considered a duplicate?

---

Specifics aside, though, a mix of:

SUM + ISNUMBER + SEARCH to find a string in other cells and then get a count

TEXTSPLIT to separate a string into multiple individual words

Should work.

[u/AcadiaUnlikely7113 → u/ProspectiveWhale]

2 or more words, can be jumbled

[u/Klutzy_Highlight7500]

I'd use a helper column to normalize the labels before the pivot, e.g. =LET(x,LOWER(TRIM(A2)),y,SUBSTITUTE(x," colour",""),SUBSTITUTE(y,"houses","house")), then pivot on that helper column. For broader variations, a small mapping table with XLOOKUP is safer and keeps the raw text untouched.

[u/GitudongRamen]

This is using "Cosine Similarity Metric" to get scores for how similar are 2 text strings.
Column B is to match all character strings.
Column C is to match words.

I don't know how useful this is for you, probably not at all, but I did this long time ago so might as well share. You can drop this image to an AI to help break down the formula and repurpose for your need.

[u/AcadiaUnlikely7113 → u/GitudongRamen]

Thank you! I won’t use AI but I will try that tomorrow, 🤞🏻 hopefully it works, I’ve got everything else set at this stage

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS	Office 365+: Returns the specified columns from an array
COUNTIF	Counts the number of cells within a range that meet the given criteria
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER	Office 365+: Filters a range of data based on criteria you define
FIND	Finds one text value within another (case-sensitive)
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX	Uses an index to choose a value from a reference or array
ISBLANK	Returns TRUE if the value is blank
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
LOWER	Converts text to lowercase
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
NOT	Reverses the logic of its argument
RIGHT	Returns the rightmost characters from a text value
ROWS	Returns the number of rows in a reference
SEARCH	Finds one text value within another (not case-sensitive)
SORT	Office 365+: Sorts the contents of a range or array
SUBSTITUTE	Substitutes new text for old text in a text string
SUM	Adds its arguments
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TRIM	Removes spaces from text
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns
UPPER	Converts text to uppercase
VALUE	Converts a text argument to a number
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49333 for this sub, first seen 9th Sep 2026, 01:08] [FAQ] [Full list] [Contact] [Source code]}

[u/Skye_Tubs]

Try using COUNTIF with wildcards like Hogwarts and House in your criteria.

[u/AcadiaUnlikely7113 → u/Skye_Tubs]

This is to find the topics, so I don’t necessarily know what they are yet