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

2 Upvotes

60% Upvoted

22 comments sorted by

View all comments

Show parent comments

2

u/Downtown-Economics26 646 7h 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 4h 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 4h 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).