P
Person
Hello, Excel gurus -
I am hoping that describing this problem accurately will be harder
for me than giving advice about it will be for you
All of my calculations need to be done within one workbook, but my
data is spread out over several worksheets. Some of the worksheets
contain data in this format:
(Region) (Subregion) (Amount)
1 2 $100
1 2 $400
1 3 $1000
2 1 $300
That is, each region (1 through whatever) contains several
subregions. Every row represents one transaction
There is another worksheet in this workbook, with the following columns:
Manager Region Subregion
1 1 2
1 1 3
2 2 1
That is, the second worksheet is basically a lookup table that tells
me which region/subregion each manager has control over. Most
managers control more than one region/subregion.
The Goal: what I'm supposed to do is to fill in a summary worksheet
having the following format (5 columns):
(Manager)(# Trans.) (Amount < $500)(Amount $500-100) (Amount > 1000)
That is, I'm supposed to use the lookup table to find out which
manager controls which region & subregion, then summarize the data
by saying how many transactions a given manager has overseen, and
indicating what percentage of that manager's transactions were less
than $500, between $500-$1,000, and greater than $1,000 (by number
of transactions, not by total dollar value).
I am having some serious difficulty wrapping my head around this,
but I am hoping that someone here will be able to help. I am
guessing that this could be done fairly readily with a pivot table,
but there are way too many rows of data to fit onto a single
worksheet (several hundred thousand, it looks like).
Any help would be greatly appreciated...
I am hoping that describing this problem accurately will be harder
for me than giving advice about it will be for you
All of my calculations need to be done within one workbook, but my
data is spread out over several worksheets. Some of the worksheets
contain data in this format:
(Region) (Subregion) (Amount)
1 2 $100
1 2 $400
1 3 $1000
2 1 $300
That is, each region (1 through whatever) contains several
subregions. Every row represents one transaction
There is another worksheet in this workbook, with the following columns:
Manager Region Subregion
1 1 2
1 1 3
2 2 1
That is, the second worksheet is basically a lookup table that tells
me which region/subregion each manager has control over. Most
managers control more than one region/subregion.
The Goal: what I'm supposed to do is to fill in a summary worksheet
having the following format (5 columns):
(Manager)(# Trans.) (Amount < $500)(Amount $500-100) (Amount > 1000)
That is, I'm supposed to use the lookup table to find out which
manager controls which region & subregion, then summarize the data
by saying how many transactions a given manager has overseen, and
indicating what percentage of that manager's transactions were less
than $500, between $500-$1,000, and greater than $1,000 (by number
of transactions, not by total dollar value).
I am having some serious difficulty wrapping my head around this,
but I am hoping that someone here will be able to help. I am
guessing that this could be done fairly readily with a pivot table,
but there are way too many rows of data to fit onto a single
worksheet (several hundred thousand, it looks like).
Any help would be greatly appreciated...