Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
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)=$A 2,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)=$A 2,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)=$A 2,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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
Harlan Grove wrote:
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). ... [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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
Harlan Grove wrote:
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
Harlan Grove wrote:
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?? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correlating data from different worksheets - toughie?
Harlan Grove wrote:
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
sharing rows of data across multiple worksheets within a workbook | Excel Worksheet Functions | |||
Putting data from multiple worksheets into one | Excel Discussion (Misc queries) |