How to find matches, pairs of two SUMPRODUCT?

A

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?

H

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

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 __

A

Andrea_from_GER

THX a ton!!I was "captured" on a complete different approach!!

A

Andrea_from_GER

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?

.

A

Andrea_from_GER

Pls find the answers at HectorÂ´s and Herbert posts

H

helene and gabor

Hello Andrea,

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