Double lookup count

[Rene Petersen]

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 1 Column 2
DK 1A
UK 1A
UK 2B
BE 2A
NL 2A
DK 2B
BE 2C
DK 1B
NL 2A
DK 1A
DK 1A
UK 1A
BE 2A



Matrix:
|1A |1B |1C |2A |2B |2C
BE 2 1
DK 3 1 1
NL 2
UK 2 1


Many thanks,
Rene

[Domenic]

Assumptions:

A1:B13 contains your source data

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

D2:D5 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 <[email protected]>,
"Rene Petersen" <[email protected]> wrote:

> 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 1 Column 2
> DK 1A
> UK 1A
> UK 2B
> BE 2A
> NL 2A
> DK 2B
> BE 2C
> DK 1B
> NL 2A
> DK 1A
> DK 1A
> UK 1A
> BE 2A
>
>
>
> Matrix:
> |1A |1B |1C |2A |2B |2C
> BE 2 1
> DK 3 1 1
> NL 2
> UK 2 1
>
>
> Many thanks,
> Rene

[Ron Coderre]

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-Pro


"Rene Petersen" wrote:

> 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 1 Column 2
> DK 1A
> UK 1A
> UK 2B
> BE 2A
> NL 2A
> DK 2B
> BE 2C
> DK 1B
> NL 2A
> DK 1A
> DK 1A
> UK 1A
> BE 2A
>
>
>
> Matrix:
> |1A |1B |1C |2A |2B |2C
> BE 2 1
> DK 3 1 1
> NL 2
> UK 2 1
>
>
> Many thanks,
> Rene
>

[Rene Petersen]

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

"Rene Petersen" wrote:

> 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 1 Column 2
> DK 1A
> UK 1A
> UK 2B
> BE 2A
> NL 2A
> DK 2B
> BE 2C
> DK 1B
> NL 2A
> DK 1A
> DK 1A
> UK 1A
> BE 2A
>
>
>
> Matrix:
> |1A |1B |1C |2A |2B |2C
> BE 2 1
> DK 3 1 1
> NL 2
> UK 2 1
>
>
> Many thanks,
> Rene
>