How to find matches, pairs of two SUMPRODUCT?

[Andrea_from_GER]

Dear all,
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?

 

[helene and gabor]

Hello Andrea,

It would help if you could explain a few lines of your table 2. How do you
calculate them?
Thank you


Gabor Sebo

 

[Héctor Miguel]

hi, Andrea !

given sample table (incuding titles) in [A2:G8]
and outcome table (including tiles) in [A11:G17]

try the following formula (starting in)...
[B12] =(b$11<>$a12)*sumproduct(countif(offset($b$2,{1;2;3;4;5;6},,,6),$a12)*countif(offset($b$2,{1;2;3;4;5;6},,,6),b$11))

then copy-cross up to [G12]
then copy-down [B12:G12] to fill the range [B12:G17]

test with your given example, then work-out the formula with your real data
you might need to change the constant-array => {1;2;3;4;5;6} <= with a row(... function
and change the 6 (columns) with the real columns (count) from your real data

hth,
hector.

__ OP __

 

[Herbert Seidenberg]

Excel 2007 Tables
Solution for 6x6 matrix.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_07_10a.xlsx

 

[Andrea_from_GER]

THX a ton!!I was "captured" on a complete different approach!!
Highly appreciate the efforts your made!!!

 

[Andrea_from_GER]

THX, your approach works perfect!!
Highly appreciate your kind help!!!

hi, Andrea !

given sample table (incuding titles) in [A2:G8]
and outcome table (including tiles) in [A11:G17]

try the following formula (starting in)...
[B12] =(b$11<>$a12)*sumproduct(countif(offset($b$2,{1;2;3;4;5;6},,,6),$a12)*countif(offset($b$2,{1;2;3;4;5;6},,,6),b$11))

then copy-cross up to [G12]
then copy-down [B12:G12] to fill the range [B12:G17]

test with your given example, then work-out the formula with your real data
you might need to change the constant-array => {1;2;3;4;5;6} <= with a row(... function
and change the 6 (columns) with the real columns (count) from your real data

hth,
hector.

__ OP __

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?

.

 

[Andrea_from_GER]

Pls find the answers at Hector´s and Herbert posts

 

[helene and gabor]

Hello Andrea,

Your table could have column headings as:
A B C D......X Y Z.
For each task that has earned say an A,B and an X you could enter a "1" for
the column representing A, B and X. (maybe zeroes for all non assigned
letters).

Assuming A.s are entered in column F
B.s are entered in column G
and A1=1

To count how many tasks have earned an A and a B
the formula would say:
=SUMPRODUCT(($F$2:$F$20=A1)*($G$2:$G$20=A1))

Best wishes

Gabor Sebo