Thread: Compare cells within several groups

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

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

Doesn't matter if they are 7 characters long.