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?

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

"Andrea_from_GER" <(E-Mail Removed)> wrote in message
news:EEC5FC21-3341-442C-B25F-(E-Mail Removed)...
> 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?
>
>
>

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?

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

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




"Herbert Seidenberg" wrote:

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

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

"Héctor Miguel" wrote:

> 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?
>
>
> .
>

Pls find the answers at Hector´s and Herbert posts

"helene and gabor" wrote:

> 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
>
> "Andrea_from_GER" <(E-Mail Removed)> wrote in message
> news:EEC5FC21-3341-442C-B25F-(E-Mail Removed)...
> > 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?
> >
> >
> >
>

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





"Andrea_from_GER" <(E-Mail Removed)> wrote in message
news:EEC5FC21-3341-442C-B25F-(E-Mail Removed)...
> 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?
>
>
>