How to search for duplicates in a string across a range of cells?

Originally Posted by NBVC

Well your notes refer to column B only...

anyways try:

=IF(F16="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(F16,A16:C16))),"exists","not existing"))

copied down

Thanks for your help. Nearly got there but I'll leave it there. Feel as though I'm pushing too hard on a difficult to explain scenario.

I was basically trying to compare the grouped cells of one column to the grouped cells of a range of columns.

Thanks again, very much appreciated.

tomlini

Originally Posted by NBVC

Still a little confused, but maybe this?

Try this formula:

=IF(SUM(IF(ISNA(MATCH("*"&$F$16:$F$25&"*",TEXT($A$16:$A$25,"########"),0)),IF(ISNA(MATCH("*"&$F$16:$F$25&"*",TEXT($B$16:$B$25,"########"),0)),IF(ISNA(MATCH("*"&$F$16:$F$25&"*",TEXT($C$16:$C$25,"########"),0)),1)))),"non existing","existing")

and confirm it with CTRL+SHIFT+ENTER so that the { } brackets appear.

This checks to see if all of the items in F16:F25 exist in all of the range A16:C25.. (all have to exist somewhere in that range)

Is that what you need?

Not quite. I suspect this will have to be a programmatic solution.

I'm looking for the whole range to be validated (A16:C25) but true returned only when the F grouped cells exist fully in one or more grouped column.

Columns to search
A B C
1 2 3
1 2 3
1 2 3
1.......so on

User Input (F)
1 exists |
1 exists |all exist in column A so valid so far
1 exists |
2 not existing | does not exist in column A so flag up to user that this permutation doesn't exist in ANY of the columns (it may well exist across the range but NOT an individual column)

Thanks again but please don't feel obliged :-)