excel - Compare multiple data from rows -

i'm looking way compare multiple rows data each other, trying find best possible match. each number in every column must approximately match other numbers in same column.

example:

• customer #1: 1 5 10 9 7 7 8 2 3
• customer #2: 10 5 9 3 5 7 4 3 2
• customer #3: 1 4 10 9 8 7 6 2 2
• customer #4: 9 5 6 7 2 1 10 5 6

in example customer #1 , #3 quite similar, , need find way highlight or sort rows can find best match.

i've tried using conditional formatting highlight numbers similar, quite confusing, because amount of data quite big.

any ideas of how solve this?

thanks!

the following formula entered in (say) l1 , pulled down gives best match current row based on sum of absolute differences between corresponding cells:-

=min(if(row($c$1:$k$4)<>row(),(mmult(abs($c1:$k1-$c$1:$k$4),transpose(column($c$1:$k$4))^0)))) 

it array formula , must entered ctrlshiftenter.

you can sort on column l bring customers lowest similarity scores top or use conditional formatting highlight rows similarity value.

edit

if wanted penalise large differences in individual columns more heavily small differences try , avoid pairs of customers similar except having columns different, try square of differences:-

=min(if(row($c$1:$k$4)<>row(),(mmult(($c1:$k1-$c$1:$k$4)^2,transpose(column($c$1:$k$4))^0)))) 

then scores test data come out 7,127,7,127.