software

I am trying to make either a VBA automation or a formula to calculate the
below.

The data is found in two columns and every time there is a match it need to
increment the matrix table.

The matrix will have about 100 columns and 200 rows, so it is rather large.
And the data will have about 2-3000 records.

Can anyone solve this challenge?

Data:
Column 1Column 2
DK1A
UK1A
UK2B
BE2A
NL2A
DK2B
BE2C
DK1B
NL2A
DK1A
DK1A
UK1A
BE2A
Matrix:
|1A|1B|1C|2A|2B|2C
BE 2 1
DK 3 1 1
NL 2
UK 2 1Many thanks,
ReneAssumptions:

A1:B13 contains your source data

E1:J1 contains the column labels, 1A, 1B, 1C, etc.

D25 contains your row labels, BE, DK, NL, and UK

Formula:

E2, copied down and across:

=SUMPRODUCT(--($A$1:$A$13=$D2),--($B$1:$B$13=E$1))

Hope this helps!

In article gt;,
quot;Rene Petersenquot; gt; wrote:

gt; I am trying to make either a VBA automation or a formula to calculate the
gt; below.
gt;
gt; The data is found in two columns and every time there is a match it need to
gt; increment the matrix table.
gt;
gt; The matrix will have about 100 columns and 200 rows, so it is rather large.
gt; And the data will have about 2-3000 records.
gt;
gt; Can anyone solve this challenge?
gt;
gt; Data:
gt; Column 1Column 2
gt; DK1A
gt; UK1A
gt; UK2B
gt; BE2A
gt; NL2A
gt; DK2B
gt; BE2C
gt; DK1B
gt; NL2A
gt; DK1A
gt; DK1A
gt; UK1A
gt; BE2A
gt;
gt;
gt;
gt; Matrix:
gt; |1A|1B|1C|2A|2B|2C
gt; BE 2 1
gt; DK 3 1 1
gt; NL 2
gt; UK 2 1
gt;
gt;
gt; Many thanks,
gt; Rene

With your data list in cells A1:B14
and your matrix in cells A16:F20
Try this:
B17: =SUMPRODUCT(($A$2:$A$14=$A17)*($B$2:$B$14=B$16))
Copy that formula throughout the matrix counting cells (B17:F20)

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Rene Petersenquot; wrote:

gt; I am trying to make either a VBA automation or a formula to calculate the
gt; below.
gt;
gt; The data is found in two columns and every time there is a match it need to
gt; increment the matrix table.
gt;
gt; The matrix will have about 100 columns and 200 rows, so it is rather large.
gt; And the data will have about 2-3000 records.
gt;
gt; Can anyone solve this challenge?
gt;
gt; Data:
gt; Column 1Column 2
gt; DK1A
gt; UK1A
gt; UK2B
gt; BE2A
gt; NL2A
gt; DK2B
gt; BE2C
gt; DK1B
gt; NL2A
gt; DK1A
gt; DK1A
gt; UK1A
gt; BE2A
gt;
gt;
gt;
gt; Matrix:
gt; |1A|1B|1C|2A|2B|2C
gt; BE 2 1
gt; DK 3 1 1
gt; NL 2
gt; UK 2 1
gt;
gt;
gt; Many thanks,
gt; Rene
gt;

Thank you Domenic and Ron, both your answers helped.

I were looking forward to sleepless nights figuring this one out. It is so
great that people can help each other like this.

Cheers,
Rene

quot;Rene Petersenquot; wrote:

gt; I am trying to make either a VBA automation or a formula to calculate the
gt; below.
gt;
gt; The data is found in two columns and every time there is a match it need to
gt; increment the matrix table.
gt;
gt; The matrix will have about 100 columns and 200 rows, so it is rather large.
gt; And the data will have about 2-3000 records.
gt;
gt; Can anyone solve this challenge?
gt;
gt; Data:
gt; Column 1Column 2
gt; DK1A
gt; UK1A
gt; UK2B
gt; BE2A
gt; NL2A
gt; DK2B
gt; BE2C
gt; DK1B
gt; NL2A
gt; DK1A
gt; DK1A
gt; UK1A
gt; BE2A
gt;
gt;
gt;
gt; Matrix:
gt; |1A|1B|1C|2A|2B|2C
gt; BE 2 1
gt; DK 3 1 1
gt; NL 2
gt; UK 2 1
gt;
gt;
gt; Many thanks,
gt; Rene
gt;