A

#### Andrea_from_GER

I am facing the following challenge:

a given table consists out of a couple of rows/columns

the columns represent a cetain task, while the rows represent a certain

group. The table is filled with a ranking from A-Z

Goal is to find matching pairs within rows = how often occurs certain

combination in a row!!! Then those results should be summed up ...

the real example occupies more than 6 columns and has about 1000 rows, so it

can not be transposed in Excel 2003

e.g. sample table

1 2 3 4 5 6

1 A B C D

2 B F

3 A B

4 C D F

5 X Y Z

6 A C F X Y Z

outcome = table with the desired results

A B C D E F

A 0 2 2 1 0 1

B 2 0 1 1 0 1

C 2 1 0 2 0 2

D 1 1 2 0 0 1

E 0 0 0 0 0 0

F 1 1 2 1 0 0

I tried a sumproduct SUMPRODUCT((B3:B8="A")*($C$3:$G$8="B")), but this

works only as long as columns are employed ....

How can the transformation above be done?