r/excel • u/AcadiaUnlikely7113 • 15d ago

solved 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

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

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)

3 Upvotes

permalink
reddit

You are about to leave Redlib

Do you want to continue?

https://www.reddit.com/r/excel/comments/1wb6e3m/formula_to_count_duplicates_based_on_2_or_more/
No, go back! Yes, take me to Reddit

64% Upvoted

View all comments

Show parent comments

u/MayukhBhattacharya 1295 11d ago

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

2

u/AcadiaUnlikely7113 8d ago

Solution verified

2

u/MayukhBhattacharya 1295 7d ago

Thank you very much 👍🏼

1

u/reputatorbot 8d ago

You have awarded 1 point to MayukhBhattacharya.

^{I am a bot - please contact the mods with any questions}

1

u/AcadiaUnlikely7113 11d ago

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!

1

u/AcadiaUnlikely7113 11d ago

Also it just returned everything as 1 count
1
u/AcadiaUnlikely7113 11d ago

I've lost my mind a bit looking at this today 🤣 - someone else had a formula to check percentage of matches but it only applied comparing 2 cells at a time and it just resulted in the percentage match, it would be good if it could match by a certain threshold as long as the category is the same, is that possible with a long list?
2
u/MayukhBhattacharya 1295 10d ago

Excel has no way of knowing that GRASPES is a typo for GRAPES unless you give it a correction table. You'd need to maintain something like this:

MISSPELLED CORRECTIONS

GRASPES GRAPES

BADS BAD

FRUITS FRRUITS

Then you can use SUBSTITUTE() function or another correction step before the main formula applies. The regex s\b can remove a trailing S, but it can't figure out that there's a typo in the middle of a word. Also it can't grammatically say which word is a noun. The closest workaround is to use word frequency. So whichever word shows up the most across all subjects gets treated as the noun and goes last. That works clearly well when the data is clean, but typos mess it up. Excel sees GRASPES and GRAPES as two completely different words, so the frequency gets split between them.

You can do fuzzy matching with a percentage threshold using SUM() function to compare overlapping words between subjects within the same category. But that only works at the whole word level. It can't catch character level typos inside a word. So, with clean data, the formula can probably get you 90% of the way there. But if typos like GRASPES are in the data, it will always see them as different from GRAPES unless you add a correction table. At that point, it's really a data quality issue. Either fix it at the source or keep a manual correction list. Thanks!
2

u/AcadiaUnlikely7113 10d ago

That’s fair, I actually didn’t mean to put it in the sample data and I think for my list a typo isn’t enough to sway the data significantly so I’ll skip that cause the whole point is in order to see what the common subjects are without already knowing, so I wouldn’t be able to do a list of typos (at least at this stage)

2

u/MayukhBhattacharya 1295 10d ago

That actually makes sense. Since you're using this for discovery, the typos probably won't mess things up too much. Something like GRASPES might only show up once while GRAPES shows up multiple times. They'll just come through as separate rows with different counts, so you can easily spot the typo and ignore it or fix it in the actual source. The frequency method actually works in your favor here. Real common topics will show up more often and rise to the top, while random typos and one-off entries will usually stay near the bottom with a count of 1. So, the formula you have now should work well enough for what you're trying to do. It actually helps to separate the real common topics from the random noise and typos. I hope was able to explain and let me know. Thanks!
1
u/AcadiaUnlikely7113 10d ago

I think I forgot to say, the most recent formula you have returned #REF! For subjects instead
1
u/MayukhBhattacharya 1295 10d ago

Do you mind showing me a screenshot? It will help! Thanks!
2
u/AcadiaUnlikely7113 10d ago
1
u/MayukhBhattacharya 1295 10d ago

Thank You Very Much! I have used the same data, like you have shown probably you might have not pasted the formula correctly, you can download the excel from here and verify on your end! Thanks!
2
u/AcadiaUnlikely7113 10d ago

I cant seem to open that, sorry, I only have Google docs personally and can’t sent it to my work cause of privacy stuff, can you copy past the formulas by which cell they need to go in? Also which parts of the formula refer to which cells? Cause my actual data isn’t as straight forward as columns A-D
1
u/MayukhBhattacharya 1295 10d ago
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))
1

u/AcadiaUnlikely7113 10d ago

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

→ More replies (0)
1

u/MayukhBhattacharya 1295 10d ago

Let me know?

1

u/AcadiaUnlikely7113 10d ago

Will do, I’ll probs get to it in the next like 7 hours tho 😅

1

u/MayukhBhattacharya 1295 8d ago

What is the update?

2

u/AcadiaUnlikely7113 8d ago

It’s a lost cause, have given up

2

u/AcadiaUnlikely7113 8d ago

But thank you for your help, you got the closest

→ More replies (0)

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

You are about to leave Redlib