I have the following sample data, I can use the following formula =COUNTIF($A$1:$E$5, A1) > 1 to find exact matches which finds "Narf" and "Narf", however whatever formula I try to find partial matches which should match "Dooze" and "Dooz 2", nothing seems to work.
One I tried to write is: =AND(COUNTIF($A$1:$E$5, A1) > 1, NOT(EXACT(A1, UNIQUE($A$1:$E$5))))
There is no "Name" header btw.
| Name | Name | Name | Name | Name |
|---|---|---|---|---|
| Drog | Kamic 1 | Gehr | Smoked | Yeahnice 2 |
| Radi | Moon | Quackers | Narf | EJ |
| Mlad | Whakz | Reaper 2 | Terroraxe | Compz |
| Cleo 1 | Nighteyes | Oliver 2 | Narf | Espi |
| Dooze | Pureshadow | Hewston | Dooz 2 | Corbs |
I'm not locked into conditional formatting so if there is an easier way with just using the formula in a random cell to display maybe a count or the string of the results, which is what I initially tried but didn't seem to work either.
Thanks
CodePudding user response：
If you want to consider matches in any word or number that may be contained you can use this formula. Adapt the range A1:E5 with your actual range:
=LAMBDA(all,{all,BYROW(INDEX(SPLIT(all," ")),LAMBDA(a,IFERROR(JOIN(", ",(FILTER(all,all<>INDEX(a,,1),all<>JOIN(" ",a),REGEXMATCH(all,JOIN("|",FILTER(a,a<>"")))))))))})(FLATTEN(A1:E5))
It returns this result with a first column with all the values, and a second one with the partial matches:
