create new table from Multiple Criteria in multiple columns

[InfoSeeker]

Please help me with solving this excel 2003 task I need done on a file
received weekly. It covers five days and each pair of columns contains
numbers and the range of acct in each column will be unknown each week, so
need the formula to auto adjust.

Day 1 Day 2 Day 3 Day 4
Day 5
Col A Col B Col C Col D Col E Col F Col G Col H Col
I Col J
Acct#1 num Acct#1 num Acct#2 num Acct#3 num Acct#1 num
Acct#2 num Acct#3 num Acct#3 num Acct#4 num Acct#6 num
Acct#6 num Acct#2 num Acct#4 num Acct#2 num Acct#5 num

I need to create a new table with a row for each unique Acct # and the num
for each day.
Col A Col B Col C Col D Col E Col F Col G
Acct#1 num num num num num SUM
Acct#2 num num num num num SUM
Acct#3 num num num num num SUM
Acct#4 num num num num num SUM
Acct#5 num num num num num SUM
Acct#6 num num num num num SUM


Next I will need to count how many number of unique accts# over the five
days that falls in a certain range table to created a pie chart

101 plus range of 101 or more
51 to 100 range of 51 to 100
41 to 50 etc
31 to 40 etc
21 to 30 etc
10 to 20
8 to 9
6 to 7
4 to 5
2 to 3
0 to 1
Total

 

[ShaneDevenshire]

Hi,

I don't think you are going to get a good start on this in the Functions
area, are you prepared to use VBA?

 

[InfoSeeker]

Yes, i am willing to use VBA.


Thanks,

Hi,

I don't think you are going to get a good start on this in the Functions
area, are you prepared to use VBA?

 

[ShaneDevenshire]

Hi,

What you should do is record the steps you normally do. Open the file that
you get weekly, then choose Tools, Macro, Record New Macro, enter a name
(without spaces), click OK.

Do all the steps you do to get to the final result, (don't do anything you
don't normally do). When you are finished choose Tools, Macro, Stop Recorder.

Test the code and then let us know what doesn't work and show us the code in
a new post.