+ Reply to Thread
Results 1 to 25 of 25

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

  1. #1
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

    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. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #5
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by tomlini View Post
    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.
    Attached Files Attached Files

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #8
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #9
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by tomlini View Post
    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. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #11
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #12
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by martindwilson View Post
    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. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

    Quote Originally Posted by tomlini View Post
    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. #14
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #16
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #18
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by tomlini View Post
    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
    Attached Files Attached Files

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

  20. #20
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #21
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #22
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #23
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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?
    Last edited by NBVC; 04-07-2009 at 04:05 PM.

  24. #24
    Registered User
    Join Date
    04-07-2009
    Location
    lancashire
    MS-Off Ver
    Excel 2002
    Posts
    18

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

    Quote Originally Posted by NBVC View Post
    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. #25
    Valued Forum Contributor
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2003
    Posts
    314

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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