Closed Thread
Results 1 to 35 of 35

Compare Two Cells & Return Whats Common Between the two strings and delete the variation

  1. #1
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Compare Two Cells & Return Whats Common Between the two strings and delete the variation

    HI all,

    I have a file where there are products with variations in their name I have to create a Parent for each group, how ever the parent should not have the variation info in the title so i have to manually delete it after i create the parents.

    Im looking for a function that will compare 2 cells, (the first cell and the cell below it) and compare the differences between the 2 and return only the common part.

    ex:
    Curvaceous Womens Plus Size Black Crop Lace Trimmed Leggings - 18
    Curvaceous Womens Plus Size Black Crop Lace Trimmed Leggings - 20

    output:
    Curvaceous Womens Plus Size Black Crop Lace Trimmed Leggings -

    example:

    Nike T-shirt Red & Blue Large For Men
    Nike T-shirt Green Small For Men

    output:

    Nike T-shirt For Men



    really need some help with this ( Will give recognition and everything)

    Thanks!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    I think you need a bit of VB code for this:

    Please Login or Register  to view this content.
    To insert this:

    1. Open your workbook
    2. Press Alt-F11 to open the VB editor
    3. From the insert menu select "Module" (not "Class module")
    4. A blank edit window will open, paste the code above directly into it.
    5. Close the VB editor to return to your workbook

    You will now have a user defined function you can use in your workbook, StringMatch, so the formula:

    =StringMatch(A3,A4)

    Will return the words that are common to both cells A3 and A4.

    Hope this is what you're looking for.
    Last edited by Andrew-R; 04-04-2012 at 06:53 AM.

  3. #3
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    HI Andrew,

    THAT WAS PERFECTO!! I dont know how to thank you!!! it works perfectly!!!

    Just one more help,

    can you tweak the same thing to this time only give the differences?

    example:

    Nike Shirt For Men
    Nike Red Shirt For Men
    Nike Green Shirt For Men

    output:

    Red
    Green


    Thanks A ton!!!

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    The code to make the function output only the differences is virtually identical (it actually just needs 3 letters deleting from the original code I posted) ...

    Please Login or Register  to view this content.
    This gives you a worksheet function called CodeMisMatch, which works in the same way as before, e.g:

    =CodeMisMatch(A3,A4)

    Rather than duplicating the code it would be neater to have one function that can do either, and specify when you use the function whether you want similarities or differences, but if you're happy with a little bit of redundancy the code above will work just fine.

  5. #5
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Andrew youre a genius!!! Thanks A ton!

    last tweak,

    The first function, how can I add a replace "--" or you know any other delimiters if they occur together?

    Example:

    Nike Shirt -Red-Small Men
    Nike Shirt -Green-Small Men

    Output:

    Nike Shirt Men (instead of)
    Nike Shirt--Men


    just "/" and "-"
    Last edited by alfykunable; 04-04-2012 at 01:35 PM.

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    You can just stick the call to the new function inside a couple of SUBSTITUTE functions ...

    =SUBSTITUTE(SUBSTITUTE(=StringMatch(A3,A4)," - -","")," / /","")

  7. #7
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Remove Variation Sample Data.xlsxRemove Variation Sample Data.xlsx

    Hi Andrew,

    I was using you function for a while and it was working perfectly as long as the the varying products were directly below it when they are in different places it only removes 1 variation:

    ex:

    data:

    <Return the Removed Variation>
    Tone Sport Childrens Velour Tracksuit Red Age 2/3<-compare this and
    Tone Sport Childrens Velour Tracksuit Red Age 3/4<-this
    Tone Sport Childrens Velour Tracksuit Red Age 5/6
    Tone Sport Childrens Velour Tracksuit Red Age 7/8
    Tone Sport Childrens Velour Tracksuit Red Age 9/10
    Tone Sport Childrens Velour Tracksuit Red Age 11/12
    Tone Sport Childrens Velour Tracksuit Red Age 13
    Tone Sport Childrens Velour Tracksuit Turquoise Age 2/3<-but this is also part of the same group and is not compared
    Tone Sport Childrens Velour Tracksuit Turquoise Age 3/4
    Tone Sport Childrens Velour Tracksuit Turquoise Age 5/6
    Tone Sport Childrens Velour Tracksuit Turquoise Age 7/8
    Tone Sport Childrens Velour Tracksuit Turquoise Age 9/10
    Tone Sport Childrens Velour Tracksuit Turquoise Age 11/12
    Tone Sport Childrens Velour Tracksuit Turquoise Age 13

    I've attached a sample data workbook,

    So what should happen is all the Products belonging to the same groupID should be compared and the first Product should not have any variation info in it, im guess this has to be done with a macro?

    Please help Andrew! I have 26K products to clean up

    THanks

    Alfred

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    OK, don't panic - we can sort this out ... does your live data include the group ID code and is it sorted so that all of the group ID codes appear together?

  9. #9
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Hi Andrew, yes.

    I;ve given the sample sheet above in that thread all the groups are togther.

    Thanks!!

    Alfred

  10. #10
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    OK, with the strings to be compared in column B and the group code in column C, starting on row 2, then something along these lines, maybe:

    =StringMatch(INDEX(B:B,MATCH(C2,C:C,0)),B2)

    Copied down - that will compare each value in cell B with the first entry for that group code, rather than the cell immediately before it.

  11. #11
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Remove Variation Sample Data - Andrews Solution1.xlsx

    Hi Andrew,

    I tried that, but its not what need. Im attaching the sheet with the output that the formula gives and the expected output.

    Thanks

    Alfred

  12. #12
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Alfred,

    I'm struggling a bit with this one - your expected output seems to now by the entire string, which is surely what you started with.

    Are you trying to get to the point where the first row of a new group contains the common text for that group and then the other rows in the same group contain just the differences?

  13. #13
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Quote Originally Posted by Andrew-R View Post
    Alfred,

    I'm struggling a bit with this one - your expected output seems to now by the entire string, which is surely what you started with.

    Are you trying to get to the point where the first row of a new group contains the common text for that group and then the other rows in the same group contain just the differences?
    "Are you trying to get to the point where the first row of a new group contains the common text for that group" Thats all

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Here's my version

    In cell E2

    =IF(C1<>C2,GetMaxCommon(B2,$B$2:$C$51,C2,FALSE),"")

    Then fill down to the bottom of the table

    Please Login or Register  to view this content.
    Remove Variation With Function.xlsm
    Last edited by jindon; 04-13-2012 at 06:06 AM.

  15. #15
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    @jindon THATS EXACTLY what i was looking for thank you so much!

    Thank you Andrew for the first two functions!! with all these now I can finish my work in mins

    THANKS

    Alfred

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Re your PM

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    12-13-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Hi Jindon,

    Your Function works well if the variation info is at the end of the Product name only,

    Nike Shoes for Men Red Small 1
    Nike Shoes for Men Green Small 1
    Nike Shoes for Men Blue Small 1

    Output: Nike Shoes for Men


    But it fails when the variation info changes places


    Nike Shoes for Men Red Small 1
    Nike Shoes for Men Green Small 1
    Nike Blue Shoes for Men Small 1

    Output: Nike ( Where as it should be Nike Shoes for Men)

    Andrews function could handle that, is there a way u can fix that?

    Thanks

    Alfred

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    OK
    try this one
    Please Login or Register  to view this content.
    Last edited by jindon; 04-18-2012 at 12:53 AM.

  19. #19
    Registered User
    Join Date
    01-02-2013
    Location
    Israel
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    THIS IS GREAT! Thank you

  20. #20
    Registered User
    Join Date
    07-07-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Jindon! Thanks for your great work on this thread. I'm trying to apply the VB script for my own purposes and am not getting any output! Can you let me know what I'm doing wrong so I can fix this and get it working? I also have about 10,000 lines to go thru and using your code as an assist would make the task MUCH easier! Thanks in advance!
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    aliastx

    You need to open your own thread by the rule here.

    And you are asking opposite, I think...
    Attached Files Attached Files
    Last edited by jindon; 07-07-2013 at 08:00 PM.

  22. #22
    Registered User
    Join Date
    07-07-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    You, sir, are a VBA badass. I hope to get to your level of understanding of this stuff someday. Thanks for your help - this works perfectly!

    ...the only thing is (I just noticed), I cannot open the VBA code because it says it is password protected... Can you c/p the VBA here for copying into the live worksheet?

    UPDATE: I am also seeing some odd behaviors in the output of the code where a punctuation mark is in the description. Can you advise please? Thanks in advance. Sample of the errors is attached.



    And in response to the other data in the post: it was opposite of what you had originally created, but down the thread I saw the original poster had a similar need to this one and you modified your code by three lines or something like that to make it work. And in the future I will be sure to open my own thread and just reference the original one instead of posting in the original one itself. Thanks again!
    Attached Files Attached Files
    Last edited by aliastx; 07-08-2013 at 02:50 PM. Reason: Found that I cannot press Alt+F11 to access the VBA code to copy out of the sample data into the actual live workbook.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    As I said, you need to open a new thread for your own question.

  24. #24
    Registered User
    Join Date
    07-07-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Okay. I have created the new thread here:

    http://www.excelforum.com/excel-prog...90#post3315190

  25. #25
    Registered User
    Join Date
    08-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Comparing two text containing new lines

    Hi All,

    I am trying to compare two texts in a excel and both the texts contain sentence with new lines and spaces.
    I have tried both Exact and vLookup query for this ,but for new lines in the text its not working.

    Please suggest.

    Regards,
    R

  26. #26
    Registered User
    Join Date
    02-14-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Hi, I had a similar request to the original poster, but when I tried this all I get is #Name? I am wondering if it is because the text in my cells is more complex than the OP's request? My cells contain sample ID names which have numbers, text and underscores. Should this still work?

    Thanks!

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Canvasshoes,

    Please open a new thread for your own question.

  28. #28
    Registered User
    Join Date
    12-15-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    this code is not working i am getting error message

  29. #29
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Hello @babuvi151 and Welcome to the Forum,

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  30. #30
    Registered User
    Join Date
    08-20-2018
    Location
    India
    MS-Off Ver
    Office365
    Posts
    1

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Hi,

    i have a same query, but wanted to solve only from Excel. Can anyone help me to solve the same problem using excel.

    Many thanks in advance.

    Regards
    Gourav

  31. #31
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    k.gourav1261 welcome to the forum

    Perhaps you missed the post directly above yours?
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  32. #32
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807
    Great work
    Last edited by BRISBANEBOB; 07-20-2020 at 06:41 PM.

  33. #33
    Registered User
    Join Date
    10-22-2020
    Location
    DELHI
    MS-Off Ver
    2016
    Posts
    2

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Unable to copy the thread

  34. #34
    Registered User
    Join Date
    10-22-2020
    Location
    DELHI
    MS-Off Ver
    2016
    Posts
    2

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    does this work for two cells only

  35. #35
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Compare Two Cells & Return Whats Common Between the two strings and delete the variati

    Hello kishore verma and Welcome to Excel Forum.
    Please open a new thread instead of breaking rule #4 (Do not post a new help request in an existing thread).
    As you're question is related to this thread you can copy the title of the thread from the first post.
    Members will be able to use the Advanced Search and find this thread (I have tested).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

Closed Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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