|
Complicated Index/Match formula
I am trying to create a final data spread sheet that is automatically filled
in from a raw data page, where the raw data is imported from a dicoverer query. below is an example of my data. Raw Data: Sheet 1 - Cells A5:E13 Shop TSD 1-May-09 4-May-09 5-May-09 04 PCKE 04 PJK3 04 Shop Total 18 BOKB 1.00 1.00 2.00 18 Shop Total 1.00 1.00 2.00 19 JMMX 2.13 1.00 19 Shop Total 2.13 1.00 24 JTKT 1.00 1.00 1.00 This sheet will only have data for certain days. --------------------------- Final Data: Sheet 2: cells A3:I15 Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May 04 PCKE C4 0.00 #DIV/0! 04 PJK3 0.00 #DIV/0! 04 Shop Total 0.00 #DIV/0! 18 BOKB 0.00 #DIV/0! 18 Shop Total 0.00 #DIV/0! 19 JMMX 0.00 #DIV/0! 19 Shop Total 0.00 #DIV/0! 24 JTKT 0.00 #DIV/0! 24 Shop Total 0.00 #DIV/0! 30 PCKJ 0.00 #DIV/0! 30 PJK1 0.00 #DIV/0! 30 PJKM 0.00 #DIV/0! Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet will show everyday with a week designated as Saturday thru Friday. What I need to do is populate the columns that have dates for the column headers with the appropriate data from the raw data sheet. Example would be C4 would be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3 (date) being looked up on the raw data sheet for the matching Column A & B and the correct date. Any help with this would be greatly appreciated |
Complicated Index/Match formula
SSG QuarterMaster wrote:
I am trying to create a final data spread sheet that is automatically filled in from a raw data page, where the raw data is imported from a dicoverer query. below is an example of my data. Raw Data: Sheet 1 - Cells A5:E13 Shop TSD 1-May-09 4-May-09 5-May-09 04 PCKE 04 PJK3 04 Shop Total 18 BOKB 1.00 1.00 2.00 18 Shop Total 1.00 1.00 2.00 19 JMMX 2.13 1.00 19 Shop Total 2.13 1.00 24 JTKT 1.00 1.00 1.00 This sheet will only have data for certain days. --------------------------- Final Data: Sheet 2: cells A3:I15 Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May 04 PCKE C4 0.00 #DIV/0! 04 PJK3 0.00 #DIV/0! 04 Shop Total 0.00 #DIV/0! 18 BOKB 0.00 #DIV/0! 18 Shop Total 0.00 #DIV/0! 19 JMMX 0.00 #DIV/0! 19 Shop Total 0.00 #DIV/0! 24 JTKT 0.00 #DIV/0! 24 Shop Total 0.00 #DIV/0! 30 PCKJ 0.00 #DIV/0! 30 PJK1 0.00 #DIV/0! 30 PJKM 0.00 #DIV/0! Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet will show everyday with a week designated as Saturday thru Friday. What I need to do is populate the columns that have dates for the column headers with the appropriate data from the raw data sheet. Example would be C4 would be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3 (date) being looked up on the raw data sheet for the matching Column A & B and the correct date. Any help with this would be greatly appreciated You can use INDEX/MATCH for multiple category lookups but when the target data is numeric I find SUMPRODUCT is much easier. The technique is described he http://xldynamic.com/source/xld.SUMPRODUCT.html In either method with appropriate use of absolute row & column references you should be able to write a single formula that can be filled through entire region. |
Complicated Index/Match formula
I am not looking to sum the numbers I need to transfer them to sheet 2 in the
appropriate cell based on the left two most columns and the row geading. "smartin" wrote: SSG QuarterMaster wrote: I am trying to create a final data spread sheet that is automatically filled in from a raw data page, where the raw data is imported from a dicoverer query. below is an example of my data. Raw Data: Sheet 1 - Cells A5:E13 Shop TSD 1-May-09 4-May-09 5-May-09 04 PCKE 04 PJK3 04 Shop Total 18 BOKB 1.00 1.00 2.00 18 Shop Total 1.00 1.00 2.00 19 JMMX 2.13 1.00 19 Shop Total 2.13 1.00 24 JTKT 1.00 1.00 1.00 This sheet will only have data for certain days. --------------------------- Final Data: Sheet 2: cells A3:I15 Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May 04 PCKE C4 0.00 #DIV/0! 04 PJK3 0.00 #DIV/0! 04 Shop Total 0.00 #DIV/0! 18 BOKB 0.00 #DIV/0! 18 Shop Total 0.00 #DIV/0! 19 JMMX 0.00 #DIV/0! 19 Shop Total 0.00 #DIV/0! 24 JTKT 0.00 #DIV/0! 24 Shop Total 0.00 #DIV/0! 30 PCKJ 0.00 #DIV/0! 30 PJK1 0.00 #DIV/0! 30 PJKM 0.00 #DIV/0! Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet will show everyday with a week designated as Saturday thru Friday. What I need to do is populate the columns that have dates for the column headers with the appropriate data from the raw data sheet. Example would be C4 would be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3 (date) being looked up on the raw data sheet for the matching Column A & B and the correct date. Any help with this would be greatly appreciated You can use INDEX/MATCH for multiple category lookups but when the target data is numeric I find SUMPRODUCT is much easier. The technique is described he http://xldynamic.com/source/xld.SUMPRODUCT.html In either method with appropriate use of absolute row & column references you should be able to write a single formula that can be filled through entire region. |
Complicated Index/Match formula
My bad. SUMPRODUCT /can/ be used to do a multiple key lookup (not just
to obtain a sum), but it will not work in your two dimensional layout. Try this array formula* in Sheet2 F2 and fill right and down as needed: =INDEX(Sheet1!$C$2:$E$9,MATCH(1,($A2=Sheet1!$A$2:$ A$9)*($B2=Sheet1!$B$2:$B$9),0),MATCH(F$1,Sheet1!$C $1:$E$1,0)) *Commit array formula by pressing Ctrl+Shift+Enter (not just Enter). Note the two different usages of MATCH. The first is good for multiple keys, as you have two keys in rows. The second is simpler when only one key needs to be matched, as you have one key in columns. SSG QuarterMaster wrote: I am not looking to sum the numbers I need to transfer them to sheet 2 in the appropriate cell based on the left two most columns and the row geading. "smartin" wrote: SSG QuarterMaster wrote: I am trying to create a final data spread sheet that is automatically filled in from a raw data page, where the raw data is imported from a dicoverer query. below is an example of my data. Raw Data: Sheet 1 - Cells A5:E13 Shop TSD 1-May-09 4-May-09 5-May-09 04 PCKE 04 PJK3 04 Shop Total 18 BOKB 1.00 1.00 2.00 18 Shop Total 1.00 1.00 2.00 19 JMMX 2.13 1.00 19 Shop Total 2.13 1.00 24 JTKT 1.00 1.00 1.00 This sheet will only have data for certain days. --------------------------- Final Data: Sheet 2: cells A3:I15 Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May 04 PCKE C4 0.00 #DIV/0! 04 PJK3 0.00 #DIV/0! 04 Shop Total 0.00 #DIV/0! 18 BOKB 0.00 #DIV/0! 18 Shop Total 0.00 #DIV/0! 19 JMMX 0.00 #DIV/0! 19 Shop Total 0.00 #DIV/0! 24 JTKT 0.00 #DIV/0! 24 Shop Total 0.00 #DIV/0! 30 PCKJ 0.00 #DIV/0! 30 PJK1 0.00 #DIV/0! 30 PJKM 0.00 #DIV/0! Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet will show everyday with a week designated as Saturday thru Friday. What I need to do is populate the columns that have dates for the column headers with the appropriate data from the raw data sheet. Example would be C4 would be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3 (date) being looked up on the raw data sheet for the matching Column A & B and the correct date. Any help with this would be greatly appreciated You can use INDEX/MATCH for multiple category lookups but when the target data is numeric I find SUMPRODUCT is much easier. The technique is described he http://xldynamic.com/source/xld.SUMPRODUCT.html In either method with appropriate use of absolute row & column references you should be able to write a single formula that can be filled through entire region. |
Complicated Index/Match formula
That was just the fix I needed. Thank you.
"smartin" wrote: My bad. SUMPRODUCT /can/ be used to do a multiple key lookup (not just to obtain a sum), but it will not work in your two dimensional layout. Try this array formula* in Sheet2 F2 and fill right and down as needed: =INDEX(Sheet1!$C$2:$E$9,MATCH(1,($A2=Sheet1!$A$2:$ A$9)*($B2=Sheet1!$B$2:$B$9),0),MATCH(F$1,Sheet1!$C $1:$E$1,0)) *Commit array formula by pressing Ctrl+Shift+Enter (not just Enter). Note the two different usages of MATCH. The first is good for multiple keys, as you have two keys in rows. The second is simpler when only one key needs to be matched, as you have one key in columns. SSG QuarterMaster wrote: I am not looking to sum the numbers I need to transfer them to sheet 2 in the appropriate cell based on the left two most columns and the row geading. "smartin" wrote: SSG QuarterMaster wrote: I am trying to create a final data spread sheet that is automatically filled in from a raw data page, where the raw data is imported from a dicoverer query. below is an example of my data. Raw Data: Sheet 1 - Cells A5:E13 Shop TSD 1-May-09 4-May-09 5-May-09 04 PCKE 04 PJK3 04 Shop Total 18 BOKB 1.00 1.00 2.00 18 Shop Total 1.00 1.00 2.00 19 JMMX 2.13 1.00 19 Shop Total 2.13 1.00 24 JTKT 1.00 1.00 1.00 This sheet will only have data for certain days. --------------------------- Final Data: Sheet 2: cells A3:I15 Shop TSD 1-May Subtotal AVE 5/1 2-May 3-May 4-May 04 PCKE C4 0.00 #DIV/0! 04 PJK3 0.00 #DIV/0! 04 Shop Total 0.00 #DIV/0! 18 BOKB 0.00 #DIV/0! 18 Shop Total 0.00 #DIV/0! 19 JMMX 0.00 #DIV/0! 19 Shop Total 0.00 #DIV/0! 24 JTKT 0.00 #DIV/0! 24 Shop Total 0.00 #DIV/0! 30 PCKJ 0.00 #DIV/0! 30 PJK1 0.00 #DIV/0! 30 PJKM 0.00 #DIV/0! Sheet 2 subtotals and Averages are formulas I alreday have input. This sheet will show everyday with a week designated as Saturday thru Friday. What I need to do is populate the columns that have dates for the column headers with the appropriate data from the raw data sheet. Example would be C4 would be replaced by data based on Fnal Data: Column A & B (04PCKE) and cell C3 (date) being looked up on the raw data sheet for the matching Column A & B and the correct date. Any help with this would be greatly appreciated You can use INDEX/MATCH for multiple category lookups but when the target data is numeric I find SUMPRODUCT is much easier. The technique is described he http://xldynamic.com/source/xld.SUMPRODUCT.html In either method with appropriate use of absolute row & column references you should be able to write a single formula that can be filled through entire region. |
| All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com