Double lookup count
 

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

Double lookup count
 

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 ,
"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

Double lookup count
 

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

Double lookup count
 

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