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

Originally Posted by NBVC

In your example... in G16 enter formula:

=IF(F16="","",IF(ISNUMBER(SEARCH(F16,B16)),"exists","not existing"))

and copy down to G25...

so as you enter values in column F, G gets populated...

Is that what you were looking for?

That's it. Fantastic, much appreciated :-)

Would there be any way to highlight the column that is currently matching the user input (F)?

Originally Posted by tomlini

That's it. Fantastic, much appreciated :-)

Would there be any way to highlight the column that is currently matching the user input (F)?

I was a bit hasty with my reply.

It works for coumn B but not the others. It seems to be just comparing against column B and ignoring the rest. Would it be possible to extend this to query all columns ?

Many thanks,
tomlini

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