Find All Matching Values in Same Column

[dlc3172]

How would I find matching values in the same column? I've attached a simple example. In this example, I would want to know that "Cat", "Mouse", and "Dog" all appear more than once in the column. Keep in mind: In reality, I don't KNOW what values are going to match. That's the purpose: I want to find all matching values in a column without knowing in advance which values will match. Thanks for the help!

 

[MountainFog]

Highlight the column, Click Conditional Formating, Highlight Duplicate Values

 

[dlc3172]

Highlight the column, Click Conditional Formating, Highlight Duplicate Values

That only highlights cells with exactly the same content. I'm looking for cells that contains matching words, not exactly matching content.

 

[MountainFog]

Are you trying to match "cat" to "CAT"? Can you provide a sample dataset and highlight the ones you would want to match?

 

[dlc3172]

Are you trying to match "cat" to "CAT"? Can you provide a sample dataset and highlight the ones you would want to match?

Sorry - I just realized I forgot to attach my example file to the original post. I'm not seeing how to upload a file, so I'll post an image. Ideally, I'm looking for a text match, not case sensitive.

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not seeing how to upload a file,

You can't, but you can provide small samples that we can use/copy from (unlike an image) with XL2BB

I would want to know that "Cat", "Mouse", and "Dog" all appear more than once in the column

I assume that you want to check whole words only. I have added 'Piglet' to the sample data and assume that you do not want 'Pig' returned in the list even though that text appears in A5 and A8 of my sample data.

Would this be any use?

23 11 18.xlsm
	A	B	C
1	List		More than once
2	Dog Cat		Dog
3	Horse Cow Mouse		Cat
4	Mouse		Mouse
5	Pig Goat
6	Chicken Rooster
7	Lamb Dog
8	Dog Mule Cat Piglet
Match

Cell Formulas
Range	Formula
C2:C4	C2	=LET(u,UNIQUE(TEXTSPLIT(TEXTJOIN(" ",,A2:A8),," ")),FILTER(u,BYROW(u,LAMBDA(rw,COUNT(SEARCH(" "&rw&" "," "&A2:A8&" "))-1))))
Dynamic array formulas.

 

[dlc3172]

How would I find matching values in the same column? I've attached a simple example. In this example, I would want to know that "Cat", "Mouse", and "Dog" all appear more than once in the column. Keep in mind: In reality, I don't KNOW what values are going to match. That's the purpose: I want to find all matching values in a column without knowing in advance which values will match. Thanks for the help!

Welcome to the MrExcel board!


You can't, but you can provide small samples that we can use/copy from (unlike an image) with XL2BB


I assume that you want to check whole words only. I have added 'Piglet' to the sample data and assume that you do not want 'Pig' returned in the list even though that text appears in A5 and A8 of my sample data.

Would this be any use?

23 11 18.xlsm
	A	B	C
1	List		More than once
2	Dog Cat		Dog
3	Horse Cow Mouse		Cat
4	Mouse		Mouse
5	Pig Goat
6	Chicken Rooster
7	Lamb Dog
8	Dog Mule Cat Piglet
Match

Cell Formulas
Range	Formula
C2:C4	C2	=LET(u,UNIQUE(TEXTSPLIT(TEXTJOIN(" ",,A2:A8),," ")),FILTER(u,BYROW(u,LAMBDA(rw,COUNT(SEARCH(" "&rw&" "," "&A2:A8&" "))-1))))
Dynamic array formulas.

Yes, whole words only. But where do I paste that formula? I'm not getting any results when I try to use it.

 

[Peter_SSs]

But where do I paste that formula?

You can put the formula in any cell you want.
However, to test the concept ..

1. Start a fresh (blank) worksheet
2. Click this icon in post #6 above
   
   
   
   
   
3. Select cell A1 in the blank worksheet
4. Paste