| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with duplicates in two columns
I have 4 identical sheets that have same columns,different data.In each sheet
column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
Try this array formula
=SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
Thanks Bob for the fast reply,but theres one problem.First let me explain why
its 4 sheets,this is because all 65536 rows are used.Now the problem is that if I have order number in two sheets but date are same,this will give me count of 2 days instead of 1.Example: Sheet1 B G 7-Jan-09 678 Sheet2 B G 7-Jan-09 678 your formula will result of 2,and it should be 1. "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
Then you have a real problem I am afraid. That formula would be horrendously
slow in your circumstances. Where does the data come from, could you query the source instead. Other than that, have you tried pivoting it with multiple ranges? -- __________________________________ HTH Bob "MAANI" wrote in message ... Thanks Bob for the fast reply,but theres one problem.First let me explain why its 4 sheets,this is because all 65536 rows are used.Now the problem is that if I have order number in two sheets but date are same,this will give me count of 2 days instead of 1.Example: Sheet1 B G 7-Jan-09 678 Sheet2 B G 7-Jan-09 678 your formula will result of 2,and it should be 1. "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
What do you mean by where the data come from ? Its a workbook thats updated
daily manually ! I don't know how to query the source,nor pivoting it. "Bob Phillips" wrote: Then you have a real problem I am afraid. That formula would be horrendously slow in your circumstances. Where does the data come from, could you query the source instead. Other than that, have you tried pivoting it with multiple ranges? -- __________________________________ HTH Bob "MAANI" wrote in message ... Thanks Bob for the fast reply,but theres one problem.First let me explain why its 4 sheets,this is because all 65536 rows are used.Now the problem is that if I have order number in two sheets but date are same,this will give me count of 2 days instead of 1.Example: Sheet1 B G 7-Jan-09 678 Sheet2 B G 7-Jan-09 678 your formula will result of 2,and it should be 1. "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
You have a workbook circa 250,000 rows of data that you update manually?
Unbelievable! Pivot tables are probably your best bet, check out http://www.cpearson.com/excel/pivots.htm http://edferrero.m6.net/Pivot.html http://it.fuqua.duke.edu/public/2001...troduction.pdf http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot01.html -- __________________________________ HTH Bob "MAANI" wrote in message ... What do you mean by where the data come from ? Its a workbook thats updated daily manually ! I don't know how to query the source,nor pivoting it. "Bob Phillips" wrote: Then you have a real problem I am afraid. That formula would be horrendously slow in your circumstances. Where does the data come from, could you query the source instead. Other than that, have you tried pivoting it with multiple ranges? -- __________________________________ HTH Bob "MAANI" wrote in message ... Thanks Bob for the fast reply,but theres one problem.First let me explain why its 4 sheets,this is because all 65536 rows are used.Now the problem is that if I have order number in two sheets but date are same,this will give me count of 2 days instead of 1.Example: Sheet1 B G 7-Jan-09 678 Sheet2 B G 7-Jan-09 678 your formula will result of 2,and it should be 1. "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
Bob,its a daily time sheets for all employees turned to a data entry
person.so its updated daily,and I'm afraid that we can't change all that to pivot tables now !! "Bob Phillips" wrote: You have a workbook circa 250,000 rows of data that you update manually? Unbelievable! Pivot tables are probably your best bet, check out http://www.cpearson.com/excel/pivots.htm http://edferrero.m6.net/Pivot.html http://it.fuqua.duke.edu/public/2001...troduction.pdf http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot01.html -- __________________________________ HTH Bob "MAANI" wrote in message ... What do you mean by where the data come from ? Its a workbook thats updated daily manually ! I don't know how to query the source,nor pivoting it. "Bob Phillips" wrote: Then you have a real problem I am afraid. That formula would be horrendously slow in your circumstances. Where does the data come from, could you query the source instead. Other than that, have you tried pivoting it with multiple ranges? -- __________________________________ HTH Bob "MAANI" wrote in message ... Thanks Bob for the fast reply,but theres one problem.First let me explain why its 4 sheets,this is because all 65536 rows are used.Now the problem is that if I have order number in two sheets but date are same,this will give me count of 2 days instead of 1.Example: Sheet1 B G 7-Jan-09 678 Sheet2 B G 7-Jan-09 678 your formula will result of 2,and it should be 1. "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct with duplicates in two columns
I am just staggered that you use Excel as the source of that much data, it
is a very flakey idea IMO. If you dropped them into a database, you could run summarised queries (from Excel) against that database. I am not suggesting that you change it to pivot tables, I am saying add pivot tables to the workbook to give you a summarised view of your data. -- __________________________________ HTH Bob "MAANI" wrote in message ... Bob,its a daily time sheets for all employees turned to a data entry person.so its updated daily,and I'm afraid that we can't change all that to pivot tables now !! "Bob Phillips" wrote: You have a workbook circa 250,000 rows of data that you update manually? Unbelievable! Pivot tables are probably your best bet, check out http://www.cpearson.com/excel/pivots.htm http://edferrero.m6.net/Pivot.html http://it.fuqua.duke.edu/public/2001...troduction.pdf http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot01.html -- __________________________________ HTH Bob "MAANI" wrote in message ... What do you mean by where the data come from ? Its a workbook thats updated daily manually ! I don't know how to query the source,nor pivoting it. "Bob Phillips" wrote: Then you have a real problem I am afraid. That formula would be horrendously slow in your circumstances. Where does the data come from, could you query the source instead. Other than that, have you tried pivoting it with multiple ranges? -- __________________________________ HTH Bob "MAANI" wrote in message ... Thanks Bob for the fast reply,but theres one problem.First let me explain why its 4 sheets,this is because all 65536 rows are used.Now the problem is that if I have order number in two sheets but date are same,this will give me count of 2 days instead of 1.Example: Sheet1 B G 7-Jan-09 678 Sheet2 B G 7-Jan-09 678 your formula will result of 2,and it should be 1. "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0)) +SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0)) Enter it with Ctrl-Shift-Enter, not Enter -- __________________________________ HTH Bob "MAANI" wrote in message ... I have 4 identical sheets that have same columns,different data.In each sheet column B has dates,and column G has order numbers,(see example below),I want to know the number of days an order took place. B G 2-Jan-09 678 2-Jan-09 555 3-Jan-09 678 4-Jan-09 555 4-Jan-09 678 4-Jan-09 678 So number of days for order 678 is 3 and for order 555 its 2.Note that order 678 or 555 could appear in more than one sheet. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT DUPLICATES | Excel Worksheet Functions | |||
| Compare columns for duplicates | New Users to Excel | |||
| Check for duplicates in 2 columns | Excel Worksheet Functions | |||
| HOW TO LOCATE DUPLICATES ON TWO COLUMNS | Excel Discussion (Misc queries) | |||
| Identifying Duplicates in Columns | Excel Worksheet Functions | |||
Linear Mode
