Difficult macro to calculate and format data
 

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

--
Message posted from http://www.ExcelForum.com

Difficult macro to calculate and format data
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

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
B column which is the result. The A column is a date which is using
for 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 B
col. for the 1

So as u can easy see now the 1 (Column's C second number) was found 2
times in the 1st position, 1 time in the 2nd position and 1 time in
the 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 be
helpful to me !!!!



Thanks in advance

*******************
Stathis
Patras - Greece


---
Message posted from http://www.ExcelForum.com/

Difficult macro to calculate and format data
 

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


Awaiting your suggestion

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

--
Message posted from http://www.ExcelForum.com

Difficult macro to calculate and format data
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

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


Awaiting your suggestion

*****************
Stathis
Patras - Greece


---
Message posted from http://www.ExcelForum.com/

Difficult macro to calculate and format data
 

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

Data=Pivot Table Report

--
Regards,
Tom Ogilvy


"stakar " wrote in message
...
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 B
column which is the result. The A column is a date which is using for
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 B
col. for the 1

So as u can easy see now the 1 (Column's C second number) was found 2
times in the 1st position, 1 time in the 2nd position and 1 time in the
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 be
helpful to me !!!!



Thanks in advance

*******************
Stathis
Patras - Greece


---
Message posted from http://www.ExcelForum.com/