Excel 2007 : Compare cells within several groups

[glux]

Hi guys!

Thanks to Forum Guru Marcol, I could solve the first step of my problem: to give a "similarity" score to each code from a list of 4-digit codes.

Here is the formula:

=MAX(IF(COUNTIF($A$2:$A$8,A2)-1>0,4,0),IF(SUMPRODUCT(--(LEFT($A2,3)=LEFT($A$2:$A$8,3)))-1>0,3,0),IF(SUMPRODUCT(--(LEFT($A2,2)=LEFT($A$2:$A$8,2)))-1>0,2,0),IF(SUMPRODUCT(--(LEFT($A2,1)=LEFT($A$2:$A$8,1)))-1>0,1,0))

Now this list of codes is made of several sub-lists, each of them representing a different acquirer (I'm doing my Thesis on Mergers & Acquisitions). These acquirers are identifiable by a code.

Basically, I need to give a "similarity" score for the acquisitions of a single acquirer, and not the whole list.

Here is an example:

"Acquiror's code" "Target Industry code" "Target-to-target similarity"
2003030 1311 4
2003030 1311 4
2003030 4924 0
2003030 1311 4
2003030 1382 2
--------------------
2023748 4911 2
2023748 5172 1
2023748 1311 4
2023748 4924 2
2023748 1382 2
2023748 1311 4
2023748 4931 2
2023748 5541 1
2023748 8731 0
--------------------
2024763 1311 4
2024763 1311 4

Attach you will find the excel file: target-to-target similarity 5.xlsx

Thanks in advance,

Glux

[zbor]

Here is marcol's suggestion based on fact that numbers in A column are 7 digit:

[glux]

Arrgh.... you are soo close!! At first I thought it worked! But unfortunately, column A is not only 7-digit codes, there's also letters. Do you think there's a solution?

I can give you the full list if you want: target-to-target similarity 6.xlsx

[zbor]

Doesn't matter if they are 7 characters long.

[glux]

Let me see

[glux]

OMG!! You're right!! Woohoo!! It's just because not the all lines were included in the formula!!

Thank you sooo much!!!! It's working!

Alright, that was the 2nd step of my problem!! There's a final one!! Maybe the hardest... but as I said for the 1st step, I will mark this 2nd step as solved and post a new thread, just cause I like solved problems :-D !

[glux]

Hello zbor, if you want to try to solve the final step of my problem, it would be fantastic... I just posted a new thread: http://www.excelforum.com/excel-2007...26#post2701526