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

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

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.

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

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

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

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?

Hi Andrew, yes.

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

Thanks!!

Alfred

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.

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

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?

Originally Posted by Andrew-R

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

@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

Re your PM

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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

THIS IS GREAT! Thank you

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!

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

Okay. I have created the new thread here:

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

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

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!

Canvasshoes,

Please open a new thread for your own question.

this code is not working i am getting error message

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

Unable to copy the thread

does this work for two cells only