Difficult macro to calculate and format data

[stakar]

The problem is .......

I have three columns A B & C
-----------------------------------------
A B C
-----------------------------------------
01/01/2004 1 0
01/02/2004 4 0
01/03/2004 3 0
01/05/2004 7 0
01/09/2004 1 0
01/11/2004 5 0
01/20/2004 4 0

01/09/2004 1 1
01/11/2004 1 1
01/20/2004 4 1
01/21/2004 2 1

As u can see we have the C column which is a repeating number and the
column which is the result. The A column is a date which is using fo
the order.

First of all we count the B column. We get

For the C column with the 0
2 times 1
2 times 4
1 time 3
1 time 5
1 time 7

For the C column with the 1
2 times 1
1 time 2
1 time 4

The problem .....
I want to show the above results using the following format

| 1 2 3 4 5 6 7 8 9 <--- the times
----|-------------------------------------------
C |
col.|
|
0 | 2 1 2 1 1 <---The B col
for the 0
1 | 2 1 1 <-- The
col. for the 1

So as u can easy see now the 1 (Column's C second number) was found
times in the 1st position, 1 time in the 2nd position and 1 time in th
4th.

I have already done the above using the

=SUMPRODUCT(($B$5:$B$5000=C$2)*($C$5:$C$5000=$A2)))
*the C$2 is the times
*the $A2 is the reference of the C column
*the B$5:$B$5000 is the B column
*the C$5:$C$5000 is the C column

But its extremely slow. So slow like a turtle.
If anyone knows how i can build it under VB using code it will b
helpful to me !!!!



Thanks in advance

*******************
Stathis
Patras - Greec

 

[Frank Kabel]

Hi
don't think VBA will be faster (I even would think it'll be slower).
Problem is your large range (B1:B5000)
do you really have 5000 entries?. Changing this could help

 

[stakar]

hi Frank,
No its not 5000 , its about 2000 rows.
The 5000 rows is for future purpose


Awaiting your suggestion

*****************
Stathis
Patras - Greec

 

[Frank Kabel]

Hi
so reducing the range could speed the calculation a little bit up
some other ideas:
- set the calculation to manual for this workbook and do a manual
re-calculation only if needed
- you may also have a look at pivot tables. Depending on your outcome
they may create a report you desire

 

[Tom Ogilvy]

Believe you can do this with a pivot table which would be almost
instantaneous.

Data=>Pivot Table Report