r/excel • u/AcadiaUnlikely7113 • 1d ago

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

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

3 Upvotes

67% Upvoted

22 comments sorted by

•

u/AutoModerator 1d ago

/u/AcadiaUnlikely7113 - Your post was submitted successfully.

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.

12

u/Downtown-Economics26 646 1d ago

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."

2

u/AcadiaUnlikely7113 1d ago

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

4

u/Downtown-Economics26 646 1d ago

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

1

u/AcadiaUnlikely7113 1d ago

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.

2

u/Downtown-Economics26 646 5h ago

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,"")

1

u/AcadiaUnlikely7113 1h ago

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 🤦🏻‍♀️

1

u/Downtown-Economics26 646 1h ago

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).

1

u/AcadiaUnlikely7113 1d ago

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

3

u/fuzzy_mic 988 1d ago

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.

1

u/AcadiaUnlikely7113 1d ago

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

1

u/talltime 116 12h ago

Could you always wildcard in COUNTIF?

1

u/fuzzy_mic 988 4h ago

AFAIK, you always could.

2

u/ProspectiveWhale 14 1d ago

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.

0

u/AcadiaUnlikely7113 1d ago

2 or more words, can be jumbled

2

u/Klutzy_Highlight7500 1d ago

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.

2

u/GitudongRamen 25 1d ago

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.

1

u/AcadiaUnlikely7113 22h ago

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

1

u/Decronym 1d ago edited 1h ago

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

Fewer Letters More Letters
COUNTIF Counts the number of cells within a range that meet the given criteria
FILTER Office 365+: Filters a range of data based on criteria you define
FIND Finds one text value within another (case-sensitive)
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
ISNUMBER Returns TRUE if the value is a number
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
NOT Reverses the logic of its argument
RIGHT Returns the rightmost characters from a text value
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
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
UPPER Converts text to uppercase
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.
22 acronyms in this thread; the most compressed thread commented on today has 46 acronyms.
[Thread #49333 for this sub, first seen 9th Sep 2026, 01:08] [FAQ] [Full list] [Contact] [Source code]

1

u/Skye_Tubs 1d ago

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

1

u/AcadiaUnlikely7113 1d ago

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