correlating data from different worksheets - toughie?

[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...

 

[Harlan Grove]

Person wrote...
....

. . . 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

Let's call this table TRANS.

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.

Call this MRSR.

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).

....

For my own simplicity, I'll assume this result table is in yet another
worksheet with the first manager ID in cell A2.

If the first column of this result table is prefilled with manager IDs
that would be found in the first column of the MRSR table, then the 2nd
through 5th columns would be

B2 [array formula]:
=SUM((INDEX(TRANS,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))

C2 [array formula]:
=SUM((INDEX(TRANS,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3))))
*(INDEX(TRANS,0,3)<500))/$B2

D2 [array formula]:
=SUM((INDEX(TRANS,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3))))
*(INDEX(TRANS,0,3)<=1000))/$B2-SUM($C2:C2)

Fill D2 right into E2. Then select B2:E2 and fill down as far as needed.

 

[Person]

Person wrote...
...

Let's call this table TRANS.


Call this MRSR.

...

[snip suggested solution]

Thanks so much - this is awesome - if I may, one quick question - if
the data that makes up TRANS (the transactions) is located on more
than one worksheet, will this solution still work? Can I name a
range TRANS that is composed of data that lives in different
worksheets? Hopefully I have not misunderstood your reply.

 

[Harlan Grove]

Person wrote...
....

Thanks so much - this is awesome - if I may, one quick question - if
the data that makes up TRANS (the transactions) is located on more
than one worksheet, will this solution still work? Can I name a
range TRANS that is composed of data that lives in different
worksheets? Hopefully I have not misunderstood your reply.

No, you can't make TRANS span multiple worksheets. If you have, say, 4
such worksheets, you'd need to name each of them, e.g., TRANS1, TRANS2,
.. . ., TRANS4 and change my formulas to something like

B2 [array formula]:

=SUM((INDEX(TRANS1,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS1,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS2,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS2,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS3,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS3,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS4,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS4,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))

There's no simple way around this. Excel is horrible at this sort of
consolidation. Other spreadsheets would be nearly as bad. (Yes, nearly
rather than more so - Excel is in a class by itself for POOR 3D
capabilities.) Databases make much, much more sense for this sort of
categorical counting/summing.

 

[Person]

Person wrote...
...

Thanks so much - this is awesome - if I may, one quick question - if
the data that makes up TRANS (the transactions) is located on more
than one worksheet, will this solution still work? Can I name a
range TRANS that is composed of data that lives in different
worksheets? Hopefully I have not misunderstood your reply.

No, you can't make TRANS span multiple worksheets. If you have, say, 4
such worksheets, you'd need to name each of them, e.g., TRANS1, TRANS2,
. . ., TRANS4 and change my formulas to something like

B2 [array formula]:

=SUM((INDEX(TRANS1,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS1,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS2,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS2,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS3,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS3,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS4,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS4,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))

There's no simple way around this. Excel is horrible at this sort of
consolidation. Other spreadsheets would be nearly as bad. (Yes, nearly
rather than more so - Excel is in a class by itself for POOR 3D
capabilities.) Databases make much, much more sense for this sort of
categorical counting/summing.

Wowser. I will give it a shot! I am very grateful for the assistance.

 

[Person]

Person wrote...
...

Thanks so much - this is awesome - if I may, one quick question - if
the data that makes up TRANS (the transactions) is located on more
than one worksheet, will this solution still work? Can I name a
range TRANS that is composed of data that lives in different
worksheets? Hopefully I have not misunderstood your reply.

No, you can't make TRANS span multiple worksheets. If you have, say, 4
such worksheets, you'd need to name each of them, e.g., TRANS1, TRANS2,
. . ., TRANS4 and change my formulas to something like

B2 [array formula]:

=SUM((INDEX(TRANS1,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS1,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS2,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS2,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS3,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS3,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS4,0,1)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS4,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$A2,INDEX(MRSR,0,3)))))

There's no simple way around this. Excel is horrible at this sort of
consolidation. Other spreadsheets would be nearly as bad. (Yes, nearly
rather than more so - Excel is in a class by itself for POOR 3D
capabilities.) Databases make much, much more sense for this sort of
categorical counting/summing.

Just by-the-by: am I wrong in believing that using Excel 2007 might
make this easier, since it can handle very large worksheets? I am
wondering whether this entire business could be handled with a pivot
table if I had everything in one place??

 

[Person]

No, you can't make TRANS span multiple worksheets. If you have, say, 4
such worksheets, you'd need to name each of them, e.g., TRANS1, TRANS2,
. . ., TRANS4 and change my formulas to something like

Looks like I'm hosed. Excel says that it cannot complete this task
with available resources. I've got 8 worksheets with TRANS data on
them - when I changed the formula per your specifications, Excel
told me the formula was too long. So I got the bright idea to name
8 formulas sum1 --> sum8, each one being '=(index[etc. etc.])'. But
when I enter the array formula {sum1+sum2} in a cell, I get the
above error message. Anything further I might try, or am I just
done here? The computer I'm using has a *ton* of memory (1.5Gb), so
either I'm entering something wrong or Excel has its own personal
memory limitations...