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

Hi All,

This forum helped me find text in one cell against a range of other cells. I would like to extend this now to rows as well.

So if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?

So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.
Columns A - D will contain a string of more than one character so it needs to search the string.
e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213
I would like this to return true because the A cells do contain the values in the E cells.

I guess this is like looking for duplicates but with a search string twist!

Hope this makes sense and any help appreciated.

Best regards,
tomlini

Originally Posted by NBVC

So are you comparing E1 to A1:D1 and E2 to A2:D2, etc separately? Not to clear.

Thanks for the prompt response and sorry for the lack of clarity.

I'm comparing E1-E5 to A1-A5, B1-B5, C1-C5 and so on.

E1-E5 will contain a single character only and the others will contain strings. I only want to return true when all the cells in the column compares are satisfied.

Hope this makes more sense.

Thanks again.

Originally Posted by tomlini

Hi All,

This forum helped me find text in one cell against a range of other cells. I would like to extend this now to rows as well.

So if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?

So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.
Columns A - D will contain a string of more than one character so it needs to search the string.
e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213
I would like this to return true because the A cells do contain the values in the E cells.

I guess this is like looking for duplicates but with a search string twist!

Hope this makes sense and any help appreciated.

Best regards,
tomlini

I am grouping the cells of E against groups of cells in other columns.

The attachment contains 2 examples. The first is probably the easiest but the second would be ideal.

Thanks again for your time, much appreciated.

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 martindwilson

its always great when a post talks about column E and the attachment doesnt match!

Can't believe how often this occurs - pesky column E

Originally Posted by NBVC

I am just confused, then why post number 10 above would not apply?

And when are we to check column B and C, then?

That's understandable. It's difficult to explain never mind digest.

The easiest way I can explain it is-

search a range of columns for text input by a user to the cells of another column. The user input must exist one of the searched columns, not across the combined range of searched columns. It would be handy to inform the user when there input is no longer valid in any of the individual searched columns.

I've tried to explain this as best I can, I apologise if this has been unclear.
Please do not feel obliged to spend your time on this if you are unclear.

Originally Posted by tomlini

Hi All,

This forum helped me find text in one cell against a range of other cells. I would like to extend this now to rows as well.

So if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?

So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.
Columns A - D will contain a string of more than one character so it needs to search the string.
e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213
I would like this to return true because the A cells do contain the values in the E cells.

I guess this is like looking for duplicates but with a search string twist!

Hope this makes sense and any help appreciated.

Best regards,
tomlini

I've attached another spreadsheet which I hope provides clarity.

Your help is much appreciated

Originally Posted by NBVC

Okay let's try this attempt...see attached..

I used a helper column (column H)

Formula in H3:

=IF(F3="","",MAX(IF(ISNUMBER(SEARCH(F3,$A$3:$C$12)),COLUMN($A$3:$C$12))))

confirmed with CTRL+SHIFT+ENTER

Formula in H4:

=IF(F4="","",IF(H3=0,0,MAX(IF(ISNUMBER(SEARCH(F4,$A$3:INDEX($A$3:$C$12,12-2,MAX(1,H3-1)))),COLUMN($A$3:INDEX($A$3:$C$12,12-2,MAX(1,H3-1)))))))

confirmed with CTRL+SHIFT+ENTER and copied down to the bottom

Formula in G3:

=IF(F3="","",IF(H3,"exists","not existing"))

copied down to the bottom.

You can hide column H, if desired.

Hopefully that's it.

Tried it, it suggests the user input is valid whether the row contains the value or not.
ie: if the first input = 2, it should output "not exists" because the first row (A3-C3) does not contain the value 2.
Seems to be checking the full range A3:C12.

Originally Posted by NBVC

I have to give up as we are going in circles...sorry... one minute is check the whole column, then next it's check just the row...

No worries, thanks for the help, appreciated.

Originally Posted by NBVC

Definitely the last attempt (because I don't like to leave things unfinished)

Replace formula in H3 with:

=IF(F3="","",MAX(IF(ISNUMBER(SEARCH(F3,$A$3:$C$3)),COLUMN($A$3:$C$3))))

confirmed with CSE keys

Replace formula in H4 with:

=IF(F4="","",IF(H3=0,0,MAX(IF(ISNUMBER(SEARCH(F4,$A4:INDEX($A4:$C4,,MAX(1,H3-1)))),COLUMN($A4:INDEX($A4:$C4,,MAX(1,H3-1)))))))

confirmed with CSE keys and copied down.

Leave formulas in G as is

Does that work?

Afraid not.

Using a real world scenario - if there were a thousand columns each containing 10 rows, i would like a user to be able to enter 10 values and establish if that selection already exists.

Not sure if that makes sense.

It's for a football syndicate I'm hoping to introducing at work!

Many thanks,
tomlini

G3 =IF(MAX(MMULT(TRANSPOSE((F3:F12=F3:F12)*1),IF(ISERROR(FIND(F3:F12,A3:C12)),0,1)))=COUNTA(F3:F12),"exists","non-exists")

Press Ctrl+Shift+Enter after inputing foruma