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

1. ## 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

2. ## Re: How to search for duplicates in a string across a range of cells?

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

3. ## Re: How to search for duplicates in a string across a range of cells?

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.

4. ## Re: How to search for duplicates in a string across a range of cells?

Just to make sure we get on the same page... can you post a sample workbook showing some data and expected results, with reasoning...

I am still not sure if you are comparing each row of data separately or you are grouping.

5. ## Re: How to search for duplicates in a string across a range of cells?

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.

6. ## Re: How to search for duplicates in a string across a range of cells?

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

7. ## Re: How to search for duplicates in a string across a range of cells?

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?

8. ## Re: 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)?

9. ## Re: How to search for duplicates in a string across a range of cells?

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

10. ## Re: How to search for duplicates in a string across a range of cells?

Well your notes refer to column B only...

anyways try:

``Please Login or Register  to view this content.``
copied down

11. ## Re: 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:

``Please Login or Register  to view this content.``
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

12. ## Re: How to search for duplicates in a string across a range of cells?

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

13. ## Re: How to search for duplicates in a string across a range of cells?

Originally Posted by tomlini
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
Still a little confused, but maybe this?

Try this formula:

``Please Login or Register  to view this content.``
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?

14. ## Re: How to search for duplicates in a string across a range of cells?

Originally Posted by NBVC
Still a little confused, but maybe this?

Try this formula:

``Please Login or Register  to view this content.``
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 :-)

15. ## Re: How to search for duplicates in a string across a range of cells?

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

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

16. ## Re: How to search for duplicates in a string across a range of cells?

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.

17. ## Re: How to search for duplicates in a string across a range of cells?

I don't mind to continue to try to get you your answer, but I am quite confused...

Can you tell me why the each of the previously supplied formulas do not work?

Each formula supplied does a different thing... none of them work?

18. ## Re: How to search for duplicates in a string across a range of cells?

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.

19. ## Re: How to search for duplicates in a string across a range of cells?

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

I used a helper column (column H)

Formula in H3:

``Please Login or Register  to view this content.``
confirmed with CTRL+SHIFT+ENTER

Formula in H4:

``Please Login or Register  to view this content.``
confirmed with CTRL+SHIFT+ENTER and copied down to the bottom

Formula in G3:

``Please Login or Register  to view this content.``
copied down to the bottom.

You can hide column H, if desired.

Hopefully that's it.

20. ## Re: How to search for duplicates in a string across a range of cells?

Originally Posted by NBVC
Okay let's try this attempt...see attached..

I used a helper column (column H)

Formula in H3:

``Please Login or Register  to view this content.``
confirmed with CTRL+SHIFT+ENTER

Formula in H4:

``Please Login or Register  to view this content.``
confirmed with CTRL+SHIFT+ENTER and copied down to the bottom

Formula in G3:

``Please Login or Register  to view this content.``
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.

21. ## Re: How to search for duplicates in a string across a range of cells?

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...

22. ## Re: How to search for duplicates in a string across a range of cells?

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.

23. ## Re: How to search for duplicates in a string across a range of cells?

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

Replace formula in H3 with:

``Please Login or Register  to view this content.``
confirmed with CSE keys

Replace formula in H4 with:
``Please Login or Register  to view this content.``
confirmed with CSE keys and copied down.

Leave formulas in G as is

Does that work?

24. ## Re: How to search for duplicates in a string across a range of cells?

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

Replace formula in H3 with:

``Please Login or Register  to view this content.``
confirmed with CSE keys

Replace formula in H4 with:
``Please Login or Register  to view this content.``
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

25. ## Re: How to search for duplicates in a string across a range of cells?

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1