compare each cell of a column with all cells of another column

[the_observer]

Hi all,

This is not a simple question and is definitely high above my limited knowledge of excel so i need your help if you may ...

I have an excel (attached) with 4 columns.
Column A has product names and Column B has their prices.
Column C has other product names and Column D has their prices.
(Actually they come from 2 different excels but i copy paste them in one excel so it will be easier for me to understand maybe).

My question is rather complicated for me (i hope it is not so much complicated for your eyes) so i split it in three parts:

1. I would like to compare each cell of column A with all cells of column B .Both cells are text (product names).

2. When a cell of columnA is equal or similar enough (e.g. first 30 characters) with a cell of columnC (which means the product is the same),i would like to export (to a new excel maybe) the product name along with the price difference (from the columns B and D that have the prices) and continue comparing with the next product of column C.

3. This has to be keep going on until all cells of column A have been compared with all cells of column C.

I have some thoughs on this riddle:
-Can excel accomplish this or should i use VBA or visual basic ?
-In step 2 is there a better way to make excel understand that the products are the same when they do not have exact names than the one i thought (by comparing the 30 first characters)?
-There might be "-" or spaces in the product names in the 2 compared columns but i guess this is the easier part since i could batch replace them.The 3 last products of the Excel i have attached are made this way for test purposes.
Let's hope i explained my problem well enough.

Now,do you think it can be done in excel ?
If yes ,i would be much obliged for any suggestions.

Thank you for your time reading this ,

Regards.

 

[vletm]

the_observer
Something like this after pressed [Do It]?

 

[Awesome Wally]

Non-VBA alternative that maybe you can use to get ideas flowing: Just quickly playing with the sample data available I can get to your desired solution with subtracting one SUMPRODUCT from another SUMPRODUCT using the first 15 characters. See the attached for an example.

The solution is a little crude but will do the job. I'm sure one of the Ninjas can provide a better solution but this one might do in a quick pinch while you wait.

 

[bosco_yip]

Price difference ( Price A - Price B )

In E2, formula copy down :

=IFERROR(B2-LOOKUP(1,-FIND(C$2:C$16,SUBSTITUTE(A2,"-"," ")),D$2:D$16),"Not found")

Regards
Bosco

 

[vletm]

the_observer
Could there be any number of rows in both sets
and that would make any number of results per Name.
Would it be easier to see which have compared? >>
There could be link to compared row's Name.
Copy Your datas and
just click [Do It].

 

[the_observer]

First of all thank you all for your answers.

It is more than obvious that there is much Excel knowledge power gathered in chandoo forum.

Judging from the results of the excel's you submitted, they all seem to do the job well ,with 2 of them failing to identify 1 product and 1 Excel (Bosco-yip) manage to catch them all.

Vletm's Excel I think it is a minor issue since the reason for not catching up a product it is that it has a "-" character in between the words so i could substitute all "-" characters with " " before i press the DO IT button.

Awasome Wally's excel misses one product (cell A5) but i do not understand why.

One important thing that i forgot to mention is that Column C will never have duplicates.

@--> Vlem
i do not understand your comment:

"Could there be any number of rows in both sets
and that would make any number of results per Name.
Would it be easier to see which have compared? >>
There could be link to compared row's Name."

Finally ,how can i see what the DO IT button has and how i change the number of letters compared?

Much obliged to all for your time and much impressed.

Regards,
Nik.

P.S. I thought this puzzle would be solved with index and match functions ...

 

[vletm]

the_observer
1) I didn't do those "-" nor " " in this version, because normally something else should change - cause some changes from original case.
2) With my version, no matter how many rows would be in both sets.
Now, there are only few ... if You copy 50-2000 rows to both sets then You no need to do nothing else than press that button as well as You would like to use like 5 rows per sets. (= no extra rows in result area)
3) Even You data has one case which has 'two results',
Don't You need to get all possible result?
4) My result shows compared prices as many there are and also link (row) to
A-column row >> You can see which rows has compared.
5) You can see code: Activate button by right mouse and check Macro ...
6) Number of letters: NOW it compares always as many letters as C-column name has per row (every row could have different length of text to use).
A-column names vary more. It's possible to make ex spinbutton to set manually number of letter if needed.
> Ideas ... Answers ... Questions?

 

[the_observer]

Vletm i am grateful for your time and your code.
What i wrote above please not be taken as a critic (i do not have the knowledge to do this anyway).
I am terribly sorry if it was taken likewise.
I was just trying to understand the different versions of the suggestions.

Nevertheless vletm's excel does the job in a supreme way.

Now,
1. I see and thought so that there was a reason not to cover the "-" or " " chars (as i wrote above).
2. That is true and is awesome
3.This is correct
4.I didn't realize that which is a superb feature.
5. Thank you.
6. I see.

Bottom line ,vlem's code does what it is supposed to do and more.

By seeing the code behind the DO IT button there is no hope i can make chances to my liking (at present time at least, i just understand the basic structure).
So without wanting to be ungrateful (as a matter of fact i am very very very
glad as the code is having in mind that i didn't expect that it could be done) i would like to kindly ask if you could add the button for the number of characters compared along with the " " ,"-" case.

Having said that, i once again thank you so much for the code you provided.
Respect and much obliged Vletm!

Kind regards,
Nik.

 

[vletm]

the_observer - Answers are always welcome.
1) " " & "-": empty ... there can be some tricky affects too?
2) There are spinbutton for number of characters (0 .. 30000).
2a) 0 is NO ACT: use C-column's lenght
2b) > 0: use number of characters to use with comparing
My opinion: This can be 'dangerous'! This can give 'interesting results'.
Test value '1' and be happy!
3) [Link]-button: enables (yellow) or disables ('blank') those links
(if disabled then can use value for calculations).
4) [Do It]-button: makes calculations.
> Ideas ... answers ... questions?
Ps. The C-column is always 'Master'.

 

[the_observer]

Hi again and sorry for my late response.

Obviously vletm , your excel has been grown to do more than expected (even a newbie like me can see it).

Once more much obliged !

Allow me though some questions which i do not understand.

1. What tricky effects could be? Can you give an example? Since i do not understand the mechanism you do the comparison of product names , my humble idea was that products names with spaces between the words are the same with product names having "-" between words.Since you know better, do you think that this feature should be removed ?

2. Amazing! Yes of course this can be dangerous.Especially with low numbers.Comparing with a minimum of e.g. 10 or 15 chars will be more adequate and less risky i believe .

3. I do not understand this.The link button is very useful to focus the products.I do not understand why to disable and what calculations should be used to.

4. The great DO IT button . Yeah!

I envy the knowledge of Excel you people have here and i bet this has not been conquered easily.

I vote Vletm for chandoo ninja !
Respect !

Regards,
Nik.

 

[vletm]

the_observer
1) Original data has " " & "-". While comparing those are same - okay?
1a) If "Kodak - 1234" > "Kodak 1234": there are three 'spaces'.
1b) There could be look like " "-characters, if make 'copy/paste from somewhere. Those would make tricky things!
2) If You compare with >0 then
"1234567" to "1234" or
"1234" to "1234567"
then the result will be different! 1st NO MATCH and 2nd MATCH!
also
'>0' ... ex with 1 You'll get interesting result as You maybe tested!
>> '0' is the most safe!
3) 'LINK': if You want to use those values for further use then NO LINK,
ex You would like to see sum of those values (=how much cheaper if...).
If You would like to see which row, then use LINK.
>> Questions are always good to get as well answers to try to help You.
>> You cannot get, if You cannot give!

 

[the_observer]

You cannot get, if You cannot give!

I wanted to start my reply with this piece of "code" the most important in this thread up to now (in my humble opinion).
Not directly relative with this forum subject but more of a stance in life i could not let it pass by .
Seems i have give enough elsewhere to get now ,here ,from Vletm (and other guys in my other questions in chandoo) and thank you all for giving.

In my subject now,

1. I believe it is ok but i will be more sure when i will put this Excel under real circumstances testing.
1a. If understand correctly what you mean,then yes, these two should be the same.
1b. I do not exactly understand what you write (maybe my English are not so good if we accept that i am not retarded) but i believe i will have more answers when the Excel will be tested under real circumstances as i wrote above.
2.Yes, low numbers will have strange results.But it will be used (depending the situation) with big numbers or with the 0.
3.Oh i see.Very Insightful from you because it will be certainly needed. I will have to find out a way to distinguish certain results (e.g. results with difference bigger than 20 or 30 euros or something like that) and i believe it will be useful there.

Knowledge is power and vletm is obviously a powerful ally

Regards,
Nik.