Formula selection: a real tough one

Folks,

This newsgroup has been extremely helpful over the years and I am thankful 
to all whom have helped me in the past. I now have a scenario that I can not 
even begin to solve and hope that someone can send me in the right 
direction.

Our ERP system can export transactional data. I would like to be able to 
analyze the data on an invoice by invoice basis to determine metrics such as 
average total invoice amount for invoices that include a specific part 
number.

The ERP data extract looks something like this:

CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr     UnitCo 
ExtCo
2301    03/12/08    100101      90873       3        5.00     15.00 
3.00      9.00
9911    03/12/08    100102      72FG6      1      75.00     75.00     42.00 
42.00
1090    03/12/08    100103      SYSTM    1    500.00    500.00    250.00 
250.00
1090    03/12/08    100103      AR987      1      20.00     20.00     10.00 
10.00
1090    03/12/08    100103      AR990      1      50.00     50.00     29.00 
29.00
1090    03/12/08    100103      SW321     1     100.00    100.00     65.00 
65.00
3125    03/12/08    100104      ACC01   10         5.00     50.00      3.00 
30.00
7865    03/12/08    100105      ACC12     2       19.00     38.00     11.00 
22.00
2029    03/12/08    100106      SYSTM    1     500.00    500.00    250.00 
250.00
2029    03/12/08    100106      AR987      1       20.00     20.00     10.00 
10.00


Assume that there are thousands of records in every extract and that the 
column headers are also the named ranges.

I would like a formula that would identify every "InvNumber" for a specific 
"ItemNo", then be able to provide a total sum for that invoice as well as 
the "CustNo".

Using the data set above, if the "ItemNo" criteria were set to "SYSTM", the 
result of the formula would be:

CustNo  Date      InvNumber    ExtPr     ExtCo
1090    03/12/08    100103     670.00    354.00
2029    03/12/08    100106     520.00    260.00

Completely at a loss here.

Thanks in advance,

David 


0
dlipetz1 (95)
4/1/2008 11:06:58 PM
excel 39879 articles. 2 followers. Follow

11 Replies
537 Views

Similar Articles

[PageSpeed] 22

Hi David

Create a Pivot Table
Place cursor in Header row>Data>Pivot Table>Finish
On the PT skeleton that appears on the new page
Drag Item No to Page area
Drag Cust No to Row area
Drag Inv Number to Row Area
Drag Date to Row Area
Drag Ext Pr to Data Area
Drag Ext Co to Data Area

Drag the Data button and drop on Total to get the two data values side by 
side.
Use the dropdown on Item No to select SYSTM

For more information on using Pivot Tables, take a look at Debra Dalgleish's 
site and scroll to Pivot Tables
http://www.contextures.com/tiptech.html
or Mike Alexander's site at
http://www.datapigtechnologies.com/ExcelMain.htm


-- 
Regards
Roger Govier

"David Lipetz" <dlipetz@hotmail.com> wrote in message 
news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
> Folks,
>
> This newsgroup has been extremely helpful over the years and I am thankful 
> to all whom have helped me in the past. I now have a scenario that I can 
> not even begin to solve and hope that someone can send me in the right 
> direction.
>
> Our ERP system can export transactional data. I would like to be able to 
> analyze the data on an invoice by invoice basis to determine metrics such 
> as average total invoice amount for invoices that include a specific part 
> number.
>
> The ERP data extract looks something like this:
>
> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr     UnitCo 
> ExtCo
> 2301    03/12/08    100101      90873       3        5.00     15.00 3.00 
> 9.00
> 9911    03/12/08    100102      72FG6      1      75.00     75.00 
> 42.00 42.00
> 1090    03/12/08    100103      SYSTM    1    500.00    500.00    250.00 
> 250.00
> 1090    03/12/08    100103      AR987      1      20.00     20.00 
> 10.00 10.00
> 1090    03/12/08    100103      AR990      1      50.00     50.00 
> 29.00 29.00
> 1090    03/12/08    100103      SW321     1     100.00    100.00     65.00 
> 65.00
> 3125    03/12/08    100104      ACC01   10         5.00     50.00 
> 3.00 30.00
> 7865    03/12/08    100105      ACC12     2       19.00     38.00 
> 11.00 22.00
> 2029    03/12/08    100106      SYSTM    1     500.00    500.00    250.00 
> 250.00
> 2029    03/12/08    100106      AR987      1       20.00     20.00 
> 10.00 10.00
>
>
> Assume that there are thousands of records in every extract and that the 
> column headers are also the named ranges.
>
> I would like a formula that would identify every "InvNumber" for a 
> specific "ItemNo", then be able to provide a total sum for that invoice as 
> well as the "CustNo".
>
> Using the data set above, if the "ItemNo" criteria were set to "SYSTM", 
> the result of the formula would be:
>
> CustNo  Date      InvNumber    ExtPr     ExtCo
> 1090    03/12/08    100103     670.00    354.00
> 2029    03/12/08    100106     520.00    260.00
>
> Completely at a loss here.
>
> Thanks in advance,
>
> David
> 
0
Roger
4/1/2008 11:30:06 PM
Thank you for the suggestion. I will check this out.

I was hoping for a programmatic way of getting this done as I'd like to be 
able to handle over a template in which the data extracts can be pasted in 
to and the formulas just do there thing. Asking users to create a Pivot 
Table may be asking for too much.


"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
> Hi David
>
> Create a Pivot Table
> Place cursor in Header row>Data>Pivot Table>Finish
> On the PT skeleton that appears on the new page
> Drag Item No to Page area
> Drag Cust No to Row area
> Drag Inv Number to Row Area
> Drag Date to Row Area
> Drag Ext Pr to Data Area
> Drag Ext Co to Data Area
>
> Drag the Data button and drop on Total to get the two data values side by 
> side.
> Use the dropdown on Item No to select SYSTM
>
> For more information on using Pivot Tables, take a look at Debra 
> Dalgleish's site and scroll to Pivot Tables
> http://www.contextures.com/tiptech.html
> or Mike Alexander's site at
> http://www.datapigtechnologies.com/ExcelMain.htm
>
>
> -- 
> Regards
> Roger Govier
>
> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>> Folks,
>>
>> This newsgroup has been extremely helpful over the years and I am 
>> thankful to all whom have helped me in the past. I now have a scenario 
>> that I can not even begin to solve and hope that someone can send me in 
>> the right direction.
>>
>> Our ERP system can export transactional data. I would like to be able to 
>> analyze the data on an invoice by invoice basis to determine metrics such 
>> as average total invoice amount for invoices that include a specific part 
>> number.
>>
>> The ERP data extract looks something like this:
>>
>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr     UnitCo 
>> ExtCo
>> 2301    03/12/08    100101      90873       3        5.00     15.00 3.00 
>> 9.00
>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 42.00 
>> 42.00
>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00    250.00 
>> 250.00
>> 1090    03/12/08    100103      AR987      1      20.00     20.00 10.00 
>> 10.00
>> 1090    03/12/08    100103      AR990      1      50.00     50.00 29.00 
>> 29.00
>> 1090    03/12/08    100103      SW321     1     100.00    100.00 
>> 65.00 65.00
>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 3.00 
>> 30.00
>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 11.00 
>> 22.00
>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00    250.00 
>> 250.00
>> 2029    03/12/08    100106      AR987      1       20.00     20.00 10.00 
>> 10.00
>>
>>
>> Assume that there are thousands of records in every extract and that the 
>> column headers are also the named ranges.
>>
>> I would like a formula that would identify every "InvNumber" for a 
>> specific "ItemNo", then be able to provide a total sum for that invoice 
>> as well as the "CustNo".
>>
>> Using the data set above, if the "ItemNo" criteria were set to "SYSTM", 
>> the result of the formula would be:
>>
>> CustNo  Date      InvNumber    ExtPr     ExtCo
>> 1090    03/12/08    100103     670.00    354.00
>> 2029    03/12/08    100106     520.00    260.00
>>
>> Completely at a loss here.
>>
>> Thanks in advance,
>>
>> David
>> 


0
dlipetz1 (95)
4/2/2008 2:03:03 AM
You could try recording a macro of yourself following the steps Roger
posted.


HTH,
JP

On Apr 1, 10:03=A0pm, "David Lipetz" <dlip...@hotmail.com> wrote:
> Thank you for the suggestion. I will check this out.
>
> I was hoping for a programmatic way of getting this done as I'd like to be=

> able to handle over a template in which the data extracts can be pasted in=

> to and the formulas just do there thing. Asking users to create a Pivot
> Table may be asking for too much.
>
0
jp2112 (204)
4/2/2008 3:00:02 AM
Once SYSTM is selected, it filters and totals for that item only messing up 
the ExtPr and ExtCo totals for the given invNumber


"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
> Hi David
>
> Create a Pivot Table
> Place cursor in Header row>Data>Pivot Table>Finish
> On the PT skeleton that appears on the new page
> Drag Item No to Page area
> Drag Cust No to Row area
> Drag Inv Number to Row Area
> Drag Date to Row Area
> Drag Ext Pr to Data Area
> Drag Ext Co to Data Area
>
> Drag the Data button and drop on Total to get the two data values side by 
> side.
> Use the dropdown on Item No to select SYSTM
>
> For more information on using Pivot Tables, take a look at Debra 
> Dalgleish's site and scroll to Pivot Tables
> http://www.contextures.com/tiptech.html
> or Mike Alexander's site at
> http://www.datapigtechnologies.com/ExcelMain.htm
>
>
> -- 
> Regards
> Roger Govier
>
> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>> Folks,
>>
>> This newsgroup has been extremely helpful over the years and I am 
>> thankful to all whom have helped me in the past. I now have a scenario 
>> that I can not even begin to solve and hope that someone can send me in 
>> the right direction.
>>
>> Our ERP system can export transactional data. I would like to be able to 
>> analyze the data on an invoice by invoice basis to determine metrics such 
>> as average total invoice amount for invoices that include a specific part 
>> number.
>>
>> The ERP data extract looks something like this:
>>
>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr     UnitCo 
>> ExtCo
>> 2301    03/12/08    100101      90873       3        5.00     15.00 3.00 
>> 9.00
>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 42.00 
>> 42.00
>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00    250.00 
>> 250.00
>> 1090    03/12/08    100103      AR987      1      20.00     20.00 10.00 
>> 10.00
>> 1090    03/12/08    100103      AR990      1      50.00     50.00 29.00 
>> 29.00
>> 1090    03/12/08    100103      SW321     1     100.00    100.00 
>> 65.00 65.00
>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 3.00 
>> 30.00
>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 11.00 
>> 22.00
>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00    250.00 
>> 250.00
>> 2029    03/12/08    100106      AR987      1       20.00     20.00 10.00 
>> 10.00
>>
>>
>> Assume that there are thousands of records in every extract and that the 
>> column headers are also the named ranges.
>>
>> I would like a formula that would identify every "InvNumber" for a 
>> specific "ItemNo", then be able to provide a total sum for that invoice 
>> as well as the "CustNo".
>>
>> Using the data set above, if the "ItemNo" criteria were set to "SYSTM", 
>> the result of the formula would be:
>>
>> CustNo  Date      InvNumber    ExtPr     ExtCo
>> 1090    03/12/08    100103     670.00    354.00
>> 2029    03/12/08    100106     520.00    260.00
>>
>> Completely at a loss here.
>>
>> Thanks in advance,
>>
>> David
>> 


0
Gerry3278 (36)
4/2/2008 2:57:56 PM
But that is the whole point about a Page Filter.
The totals will be just for that Item.

-- 
Regards
Roger Govier

"GerryGerry" <Gerry@Gerry.anon> wrote in message 
news:S9NIj.6$rk6.2@newsfet15.ams...
> Once SYSTM is selected, it filters and totals for that item only messing 
> up the ExtPr and ExtCo totals for the given invNumber
>
>
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
> news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>> Hi David
>>
>> Create a Pivot Table
>> Place cursor in Header row>Data>Pivot Table>Finish
>> On the PT skeleton that appears on the new page
>> Drag Item No to Page area
>> Drag Cust No to Row area
>> Drag Inv Number to Row Area
>> Drag Date to Row Area
>> Drag Ext Pr to Data Area
>> Drag Ext Co to Data Area
>>
>> Drag the Data button and drop on Total to get the two data values side by 
>> side.
>> Use the dropdown on Item No to select SYSTM
>>
>> For more information on using Pivot Tables, take a look at Debra 
>> Dalgleish's site and scroll to Pivot Tables
>> http://www.contextures.com/tiptech.html
>> or Mike Alexander's site at
>> http://www.datapigtechnologies.com/ExcelMain.htm
>>
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
>> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>>> Folks,
>>>
>>> This newsgroup has been extremely helpful over the years and I am 
>>> thankful to all whom have helped me in the past. I now have a scenario 
>>> that I can not even begin to solve and hope that someone can send me in 
>>> the right direction.
>>>
>>> Our ERP system can export transactional data. I would like to be able to 
>>> analyze the data on an invoice by invoice basis to determine metrics 
>>> such as average total invoice amount for invoices that include a 
>>> specific part number.
>>>
>>> The ERP data extract looks something like this:
>>>
>>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr     UnitCo 
>>> ExtCo
>>> 2301    03/12/08    100101      90873       3        5.00     15.00 3.00 
>>> 9.00
>>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 42.00 
>>> 42.00
>>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00    250.00 
>>> 250.00
>>> 1090    03/12/08    100103      AR987      1      20.00     20.00 10.00 
>>> 10.00
>>> 1090    03/12/08    100103      AR990      1      50.00     50.00 29.00 
>>> 29.00
>>> 1090    03/12/08    100103      SW321     1     100.00    100.00 65.00 
>>> 65.00
>>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 3.00 
>>> 30.00
>>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 11.00 
>>> 22.00
>>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00 
>>> 250.00 250.00
>>> 2029    03/12/08    100106      AR987      1       20.00     20.00 10.00 
>>> 10.00
>>>
>>>
>>> Assume that there are thousands of records in every extract and that the 
>>> column headers are also the named ranges.
>>>
>>> I would like a formula that would identify every "InvNumber" for a 
>>> specific "ItemNo", then be able to provide a total sum for that invoice 
>>> as well as the "CustNo".
>>>
>>> Using the data set above, if the "ItemNo" criteria were set to "SYSTM", 
>>> the result of the formula would be:
>>>
>>> CustNo  Date      InvNumber    ExtPr     ExtCo
>>> 1090    03/12/08    100103     670.00    354.00
>>> 2029    03/12/08    100106     520.00    260.00
>>>
>>> Completely at a loss here.
>>>
>>> Thanks in advance,
>>>
>>> David
>>>
>
> 
0
Roger
4/4/2008 10:38:53 PM
I understand that, but what I was pointing out was that it doesn't give the 
result the original poster required.
"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:F67D56B5-DABA-4940-9D2F-19E6A66FBECF@microsoft.com...
> But that is the whole point about a Page Filter.
> The totals will be just for that Item.
>
> -- 
> Regards
> Roger Govier
>
> "GerryGerry" <Gerry@Gerry.anon> wrote in message 
> news:S9NIj.6$rk6.2@newsfet15.ams...
>> Once SYSTM is selected, it filters and totals for that item only messing 
>> up the ExtPr and ExtCo totals for the given invNumber
>>
>>
>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
>> news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>>> Hi David
>>>
>>> Create a Pivot Table
>>> Place cursor in Header row>Data>Pivot Table>Finish
>>> On the PT skeleton that appears on the new page
>>> Drag Item No to Page area
>>> Drag Cust No to Row area
>>> Drag Inv Number to Row Area
>>> Drag Date to Row Area
>>> Drag Ext Pr to Data Area
>>> Drag Ext Co to Data Area
>>>
>>> Drag the Data button and drop on Total to get the two data values side 
>>> by side.
>>> Use the dropdown on Item No to select SYSTM
>>>
>>> For more information on using Pivot Tables, take a look at Debra 
>>> Dalgleish's site and scroll to Pivot Tables
>>> http://www.contextures.com/tiptech.html
>>> or Mike Alexander's site at
>>> http://www.datapigtechnologies.com/ExcelMain.htm
>>>
>>>
>>> -- 
>>> Regards
>>> Roger Govier
>>>
>>> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
>>> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>> Folks,
>>>>
>>>> This newsgroup has been extremely helpful over the years and I am 
>>>> thankful to all whom have helped me in the past. I now have a scenario 
>>>> that I can not even begin to solve and hope that someone can send me in 
>>>> the right direction.
>>>>
>>>> Our ERP system can export transactional data. I would like to be able 
>>>> to analyze the data on an invoice by invoice basis to determine metrics 
>>>> such as average total invoice amount for invoices that include a 
>>>> specific part number.
>>>>
>>>> The ERP data extract looks something like this:
>>>>
>>>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr     UnitCo 
>>>> ExtCo
>>>> 2301    03/12/08    100101      90873       3        5.00     15.00 
>>>> 3.00 9.00
>>>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 42.00 
>>>> 42.00
>>>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00 
>>>> 250.00 250.00
>>>> 1090    03/12/08    100103      AR987      1      20.00     20.00 10.00 
>>>> 10.00
>>>> 1090    03/12/08    100103      AR990      1      50.00     50.00 29.00 
>>>> 29.00
>>>> 1090    03/12/08    100103      SW321     1     100.00    100.00 65.00 
>>>> 65.00
>>>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 3.00 
>>>> 30.00
>>>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 11.00 
>>>> 22.00
>>>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00 250.00 
>>>> 250.00
>>>> 2029    03/12/08    100106      AR987      1       20.00     20.00 
>>>> 10.00 10.00
>>>>
>>>>
>>>> Assume that there are thousands of records in every extract and that 
>>>> the column headers are also the named ranges.
>>>>
>>>> I would like a formula that would identify every "InvNumber" for a 
>>>> specific "ItemNo", then be able to provide a total sum for that invoice 
>>>> as well as the "CustNo".
>>>>
>>>> Using the data set above, if the "ItemNo" criteria were set to "SYSTM", 
>>>> the result of the formula would be:
>>>>
>>>> CustNo  Date      InvNumber    ExtPr     ExtCo
>>>> 1090    03/12/08    100103     670.00    354.00
>>>> 2029    03/12/08    100106     520.00    260.00
>>>>
>>>> Completely at a loss here.
>>>>
>>>> Thanks in advance,
>>>>
>>>> David
>>>>
>>
>> 


0
Gerry3278 (36)
4/7/2008 10:51:35 AM
Perhaps I am being extremely dense here - not an uncommon occurrence - but 
when I took the OP's data and Pivoted it in the manner I described, I 
obtained the result layout he was seeking.

-- 
Regards
Roger Govier

"GerryGerry" <Gerry@Gerry.anon> wrote in message 
news:Y0nKj.9617$Lq5.6324@newsfe18.ams2...
> I understand that, but what I was pointing out was that it doesn't give 
> the result the original poster required.
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
> news:F67D56B5-DABA-4940-9D2F-19E6A66FBECF@microsoft.com...
>> But that is the whole point about a Page Filter.
>> The totals will be just for that Item.
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "GerryGerry" <Gerry@Gerry.anon> wrote in message 
>> news:S9NIj.6$rk6.2@newsfet15.ams...
>>> Once SYSTM is selected, it filters and totals for that item only messing 
>>> up the ExtPr and ExtCo totals for the given invNumber
>>>
>>>
>>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
>>> news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>>>> Hi David
>>>>
>>>> Create a Pivot Table
>>>> Place cursor in Header row>Data>Pivot Table>Finish
>>>> On the PT skeleton that appears on the new page
>>>> Drag Item No to Page area
>>>> Drag Cust No to Row area
>>>> Drag Inv Number to Row Area
>>>> Drag Date to Row Area
>>>> Drag Ext Pr to Data Area
>>>> Drag Ext Co to Data Area
>>>>
>>>> Drag the Data button and drop on Total to get the two data values side 
>>>> by side.
>>>> Use the dropdown on Item No to select SYSTM
>>>>
>>>> For more information on using Pivot Tables, take a look at Debra 
>>>> Dalgleish's site and scroll to Pivot Tables
>>>> http://www.contextures.com/tiptech.html
>>>> or Mike Alexander's site at
>>>> http://www.datapigtechnologies.com/ExcelMain.htm
>>>>
>>>>
>>>> -- 
>>>> Regards
>>>> Roger Govier
>>>>
>>>> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
>>>> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>>> Folks,
>>>>>
>>>>> This newsgroup has been extremely helpful over the years and I am 
>>>>> thankful to all whom have helped me in the past. I now have a scenario 
>>>>> that I can not even begin to solve and hope that someone can send me 
>>>>> in the right direction.
>>>>>
>>>>> Our ERP system can export transactional data. I would like to be able 
>>>>> to analyze the data on an invoice by invoice basis to determine 
>>>>> metrics such as average total invoice amount for invoices that include 
>>>>> a specific part number.
>>>>>
>>>>> The ERP data extract looks something like this:
>>>>>
>>>>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr 
>>>>> UnitCo ExtCo
>>>>> 2301    03/12/08    100101      90873       3        5.00     15.00 
>>>>> 3.00 9.00
>>>>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 
>>>>> 42.00 42.00
>>>>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00 250.00 
>>>>> 250.00
>>>>> 1090    03/12/08    100103      AR987      1      20.00     20.00 
>>>>> 10.00 10.00
>>>>> 1090    03/12/08    100103      AR990      1      50.00     50.00 
>>>>> 29.00 29.00
>>>>> 1090    03/12/08    100103      SW321     1     100.00    100.00 65.00 
>>>>> 65.00
>>>>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 3.00 
>>>>> 30.00
>>>>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 
>>>>> 11.00 22.00
>>>>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00 250.00 
>>>>> 250.00
>>>>> 2029    03/12/08    100106      AR987      1       20.00     20.00 
>>>>> 10.00 10.00
>>>>>
>>>>>
>>>>> Assume that there are thousands of records in every extract and that 
>>>>> the column headers are also the named ranges.
>>>>>
>>>>> I would like a formula that would identify every "InvNumber" for a 
>>>>> specific "ItemNo", then be able to provide a total sum for that 
>>>>> invoice as well as the "CustNo".
>>>>>
>>>>> Using the data set above, if the "ItemNo" criteria were set to 
>>>>> "SYSTM", the result of the formula would be:
>>>>>
>>>>> CustNo  Date      InvNumber    ExtPr     ExtCo
>>>>> 1090    03/12/08    100103     670.00    354.00
>>>>> 2029    03/12/08    100106     520.00    260.00
>>>>>
>>>>> Completely at a loss here.
>>>>>
>>>>> Thanks in advance,
>>>>>
>>>>> David
>>>>>
>>>
>>>
>
> 
0
Roger
4/7/2008 6:19:59 PM
The following describes a formula-based solution, which will give you
what you asked for:

Assuming your data with header occupies cells A1 to i11, and that you
have the summary table headers on row 20 in columns A to E, this gives
you space to select the itemNo in A18.

Insert a new column A, and put this formula in A2:

=3DE2&"_"&COUNTIF(E$2:E2,E2)

and copy this down to A11 - this will give you a sequential number
tagged on to each ItemNo.

Then put this formula in A21:

=3DIF(ISNA(MATCH(B$18&"_"&ROW(A1),A$2:A$11,0)),"",MATCH(B
$18&"_"&ROW(A1),A$2:A$11,0))

You can copy this down for as many rows as you think you may need - I
copied it to A29. The next formula goes into B21:

=3DIF($A21=3D"","",INDEX(B$2:B$11,$A21))

and this can also be copied into C21:D21. A slightly different formula
is needed for the next two cells, as follows:

E21:     =3DIF($A21=3D"","",INDEX(H$2:H$11,$A21))
F21:     =3DIF($A21=3D"","",INDEX(J$2:J$11,$A21))

and now the cells B21:F21 can be copied down for as many rows as you
think you need to cater for duplicates (eg to row 29). These cells
will all appear blank, so to finish it off put "Item Num:" as a label
in A18, set the background colour of B18 to bright yellow, and put
SYSTM in B18 - immediately the appropriate data appears in rows 21 and
22. Change B18 to a different ItemNo, and the display changes again.

I think this is what you wanted. The summary table could be on a
different sheet - Insert a new sheet and then cut/paste the summary
block from A18:F29 into the new sheet. Obviously, wherever ranges go
to $11, you should change these to suit the amount of data that you
have. If you do move the formula to a different sheet, then you could
make these into full-column references so that you don't need to
bother with row numbers.

I'm not sure where the ExtPr and ExtCo values come from in your
example.

Hope this helps.

Pete


On Apr 2, 3:03=A0am, "David Lipetz" <dlip...@hotmail.com> wrote:
> Thank you for the suggestion. I will check this out.
>
> I was hoping for a programmatic way of getting this done as I'd like to be=

> able to handle over a template in which the data extracts can be pasted in=

> to and the formulas just do there thing. Asking users to create a Pivot
> Table may be asking for too much.
>
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
>
> news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>
>
>
> > Hi David
>
> > Create a Pivot Table
> > Place cursor in Header row>Data>Pivot Table>Finish
> > On the PT skeleton that appears on the new page
> > Drag Item No to Page area
> > Drag Cust No to Row area
> > Drag Inv Number to Row Area
> > Drag Date to Row Area
> > Drag Ext Pr to Data Area
> > Drag Ext Co to Data Area
>
> > Drag the Data button and drop on Total to get the two data values side b=
y
> > side.
> > Use the dropdown on Item No to select SYSTM
>
> > For more information on using Pivot Tables, take a look at Debra
> > Dalgleish's site and scroll to Pivot Tables
> >http://www.contextures.com/tiptech.html
> > or Mike Alexander's site at
> >http://www.datapigtechnologies.com/ExcelMain.htm
>
> > --
> > Regards
> > Roger Govier
>
> > "David Lipetz" <dlip...@hotmail.com> wrote in message
> >news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
> >> Folks,
>
> >> This newsgroup has been extremely helpful over the years and I am
> >> thankful to all whom have helped me in the past. I now have a scenario
> >> that I can not even begin to solve and hope that someone can send me in=

> >> the right direction.
>
> >> Our ERP system can export transactional data. I would like to be able t=
o
> >> analyze the data on an invoice by invoice basis to determine metrics su=
ch
> >> as average total invoice amount for invoices that include a specific pa=
rt
> >> number.
>
> >> The ERP data extract looks something like this:
>
> >> CustNo =A0Date =A0 =A0 =A0InvNumber =A0 =A0ItemNo =A0QTY =A0 UnitPr =A0=
 =A0ExtPr =A0 =A0 UnitCo
> >> ExtCo
> >> 2301 =A0 =A003/12/08 =A0 =A0100101 =A0 =A0 =A090873 =A0 =A0 =A0 3 =A0 =
=A0 =A0 =A05.00 =A0 =A0 15.00 3.00
> >> 9.00
> >> 9911 =A0 =A003/12/08 =A0 =A0100102 =A0 =A0 =A072FG6 =A0 =A0 =A01 =A0 =
=A0 =A075.00 =A0 =A0 75.00 42.00
> >> 42.00
> >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0SYSTM =A0 =A01 =A0 =A0500=
..00 =A0 =A0500.00 =A0 =A0250.00
> >> 250.00
> >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0AR987 =A0 =A0 =A01 =A0 =
=A0 =A020.00 =A0 =A0 20.00 10.00
> >> 10.00
> >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0AR990 =A0 =A0 =A01 =A0 =
=A0 =A050.00 =A0 =A0 50.00 29.00
> >> 29.00
> >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0SW321 =A0 =A0 1 =A0 =A0 1=
00.00 =A0 =A0100.00
> >> 65.00 65.00
> >> 3125 =A0 =A003/12/08 =A0 =A0100104 =A0 =A0 =A0ACC01 =A0 10 =A0 =A0 =A0 =
=A0 5.00 =A0 =A0 50.00 3.00
> >> 30.00
> >> 7865 =A0 =A003/12/08 =A0 =A0100105 =A0 =A0 =A0ACC12 =A0 =A0 2 =A0 =A0 =
=A0 19.00 =A0 =A0 38.00 11.00
> >> 22.00
> >> 2029 =A0 =A003/12/08 =A0 =A0100106 =A0 =A0 =A0SYSTM =A0 =A01 =A0 =A0 50=
0.00 =A0 =A0500.00 =A0 =A0250.00
> >> 250.00
> >> 2029 =A0 =A003/12/08 =A0 =A0100106 =A0 =A0 =A0AR987 =A0 =A0 =A01 =A0 =
=A0 =A0 20.00 =A0 =A0 20.00 10.00
> >> 10.00
>
> >> Assume that there are thousands of records in every extract and that th=
e
> >> column headers are also the named ranges.
>
> >> I would like a formula that would identify every "InvNumber" for a
> >> specific "ItemNo", then be able to provide a total sum for that invoice=

> >> as well as the "CustNo".
>
> >> Using the data set above, if the "ItemNo" criteria were set to "SYSTM",=

> >> the result of the formula would be:
>
> >> CustNo =A0Date =A0 =A0 =A0InvNumber =A0 =A0ExtPr =A0 =A0 ExtCo
> >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 670.00 =A0 =A0354.00
> >> 2029 =A0 =A003/12/08 =A0 =A0100106 =A0 =A0 520.00 =A0 =A0260.00
>
> >> Completely at a loss here.
>
> >> Thanks in advance,
>
> >> David- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
4/7/2008 11:50:59 PM
It's probably me being dense, but although I got the correct layout, the 
totals were different, (as it only totaled the product selected but not for 
the entire invoice) i.e I got the following:

CustNo  Date      InvNumber    ExtPr     ExtCo
1090    03/12/08    100103     500.00    250.00
2029    03/12/08    100106     500.00    250.00

Which does not match the OP's desired table.

BTW I'm using Excel 2007


"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:E445086E-F925-4E9F-B7AB-CCA285FA8BDD@microsoft.com...
> Perhaps I am being extremely dense here - not an uncommon occurrence - but 
> when I took the OP's data and Pivoted it in the manner I described, I 
> obtained the result layout he was seeking.
>
> -- 
> Regards
> Roger Govier
>
> "GerryGerry" <Gerry@Gerry.anon> wrote in message 
> news:Y0nKj.9617$Lq5.6324@newsfe18.ams2...
>> I understand that, but what I was pointing out was that it doesn't give 
>> the result the original poster required.
>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
>> news:F67D56B5-DABA-4940-9D2F-19E6A66FBECF@microsoft.com...
>>> But that is the whole point about a Page Filter.
>>> The totals will be just for that Item.
>>>
>>> -- 
>>> Regards
>>> Roger Govier
>>>
>>> "GerryGerry" <Gerry@Gerry.anon> wrote in message 
>>> news:S9NIj.6$rk6.2@newsfet15.ams...
>>>> Once SYSTM is selected, it filters and totals for that item only 
>>>> messing up the ExtPr and ExtCo totals for the given invNumber
>>>>
>>>>
>>>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
>>>> news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>>>>> Hi David
>>>>>
>>>>> Create a Pivot Table
>>>>> Place cursor in Header row>Data>Pivot Table>Finish
>>>>> On the PT skeleton that appears on the new page
>>>>> Drag Item No to Page area
>>>>> Drag Cust No to Row area
>>>>> Drag Inv Number to Row Area
>>>>> Drag Date to Row Area
>>>>> Drag Ext Pr to Data Area
>>>>> Drag Ext Co to Data Area
>>>>>
>>>>> Drag the Data button and drop on Total to get the two data values side 
>>>>> by side.
>>>>> Use the dropdown on Item No to select SYSTM
>>>>>
>>>>> For more information on using Pivot Tables, take a look at Debra 
>>>>> Dalgleish's site and scroll to Pivot Tables
>>>>> http://www.contextures.com/tiptech.html
>>>>> or Mike Alexander's site at
>>>>> http://www.datapigtechnologies.com/ExcelMain.htm
>>>>>
>>>>>
>>>>> -- 
>>>>> Regards
>>>>> Roger Govier
>>>>>
>>>>> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
>>>>> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>>>> Folks,
>>>>>>
>>>>>> This newsgroup has been extremely helpful over the years and I am 
>>>>>> thankful to all whom have helped me in the past. I now have a 
>>>>>> scenario that I can not even begin to solve and hope that someone can 
>>>>>> send me in the right direction.
>>>>>>
>>>>>> Our ERP system can export transactional data. I would like to be able 
>>>>>> to analyze the data on an invoice by invoice basis to determine 
>>>>>> metrics such as average total invoice amount for invoices that 
>>>>>> include a specific part number.
>>>>>>
>>>>>> The ERP data extract looks something like this:
>>>>>>
>>>>>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr UnitCo 
>>>>>> ExtCo
>>>>>> 2301    03/12/08    100101      90873       3        5.00     15.00 
>>>>>> 3.00 9.00
>>>>>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 
>>>>>> 42.00 42.00
>>>>>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00 250.00 
>>>>>> 250.00
>>>>>> 1090    03/12/08    100103      AR987      1      20.00     20.00 
>>>>>> 10.00 10.00
>>>>>> 1090    03/12/08    100103      AR990      1      50.00     50.00 
>>>>>> 29.00 29.00
>>>>>> 1090    03/12/08    100103      SW321     1     100.00    100.00 
>>>>>> 65.00 65.00
>>>>>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 
>>>>>> 3.00 30.00
>>>>>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 
>>>>>> 11.00 22.00
>>>>>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00 
>>>>>> 250.00 250.00
>>>>>> 2029    03/12/08    100106      AR987      1       20.00     20.00 
>>>>>> 10.00 10.00
>>>>>>
>>>>>>
>>>>>> Assume that there are thousands of records in every extract and that 
>>>>>> the column headers are also the named ranges.
>>>>>>
>>>>>> I would like a formula that would identify every "InvNumber" for a 
>>>>>> specific "ItemNo", then be able to provide a total sum for that 
>>>>>> invoice as well as the "CustNo".
>>>>>>
>>>>>> Using the data set above, if the "ItemNo" criteria were set to 
>>>>>> "SYSTM", the result of the formula would be:
>>>>>>
>>>>>> CustNo  Date      InvNumber    ExtPr     ExtCo
>>>>>> 1090    03/12/08    100103     670.00    354.00
>>>>>> 2029    03/12/08    100106     520.00    260.00
>>>>>>
>>>>>> Completely at a loss here.
>>>>>>
>>>>>> Thanks in advance,
>>>>>>
>>>>>> David
>>>>>>
>>>>
>>>>
>>
>> 


0
Gerry3278 (36)
4/8/2008 9:14:45 AM
Having seen GerryGerry's latest post, I now understand where your
figures come from for ExtPr and ExtCo in your example - they are the
sum for each invoice. You can obtain these values by modifying these
formulae:

E21:    =3DIF($A21=3D"","",SUMIF($D$2:$D$11,D21,H$2:H$11))

F21:    =3DIF($A21=3D"","",SUMIF($D$2:$D$11,D21,J$2:J$11))

Copy these down to row 29.

This now gives you exactly what you asked for in your opening post.

Hope this helps.

Pete

On Apr 8, 12:50=A0am, Pete_UK <pashu...@auditel.net> wrote:
> The following describes a formula-based solution, which will give you
> what you asked for:
>
> Assuming your data with header occupies cells A1 to i11, and that you
> have the summary table headers on row 20 in columns A to E, this gives
> you space to select the itemNo in A18.
>
> Insert a new column A, and put this formula in A2:
>
> =3DE2&"_"&COUNTIF(E$2:E2,E2)
>
> and copy this down to A11 - this will give you a sequential number
> tagged on to each ItemNo.
>
> Then put this formula in A21:
>
> =3DIF(ISNA(MATCH(B$18&"_"&ROW(A1),A$2:A$11,0)),"",MATCH(B
> $18&"_"&ROW(A1),A$2:A$11,0))
>
> You can copy this down for as many rows as you think you may need - I
> copied it to A29. The next formula goes into B21:
>
> =3DIF($A21=3D"","",INDEX(B$2:B$11,$A21))
>
> and this can also be copied into C21:D21. A slightly different formula
> is needed for the next two cells, as follows:
>
> E21: =A0 =A0 =3DIF($A21=3D"","",INDEX(H$2:H$11,$A21))
> F21: =A0 =A0 =3DIF($A21=3D"","",INDEX(J$2:J$11,$A21))
>
> and now the cells B21:F21 can be copied down for as many rows as you
> think you need to cater for duplicates (eg to row 29). These cells
> will all appear blank, so to finish it off put "Item Num:" as a label
> in A18, set the background colour of B18 to bright yellow, and put
> SYSTM in B18 - immediately the appropriate data appears in rows 21 and
> 22. Change B18 to a different ItemNo, and the display changes again.
>
> I think this is what you wanted. The summary table could be on a
> different sheet - Insert a new sheet and then cut/paste the summary
> block from A18:F29 into the new sheet. Obviously, wherever ranges go
> to $11, you should change these to suit the amount of data that you
> have. If you do move the formula to a different sheet, then you could
> make these into full-column references so that you don't need to
> bother with row numbers.
>
> I'm not sure where the ExtPr and ExtCo values come from in your
> example.
>
> Hope this helps.
>
> Pete
>
> On Apr 2, 3:03=A0am, "David Lipetz" <dlip...@hotmail.com> wrote:
>
>
>
> > Thank you for the suggestion. I will check this out.
>
> > I was hoping for a programmatic way of getting this done as I'd like to =
be
> > able to handle over a template in which the data extracts can be pasted =
in
> > to and the formulas just do there thing. Asking users to create a Pivot
> > Table may be asking for too much.
>
> > "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
>
> >news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>
> > > Hi David
>
> > > Create a Pivot Table
> > > Place cursor in Header row>Data>Pivot Table>Finish
> > > On the PT skeleton that appears on the new page
> > > Drag Item No to Page area
> > > Drag Cust No to Row area
> > > Drag Inv Number to Row Area
> > > Drag Date to Row Area
> > > Drag Ext Pr to Data Area
> > > Drag Ext Co to Data Area
>
> > > Drag the Data button and drop on Total to get the two data values side=
 by
> > > side.
> > > Use the dropdown on Item No to select SYSTM
>
> > > For more information on using Pivot Tables, take a look at Debra
> > > Dalgleish's site and scroll to Pivot Tables
> > >http://www.contextures.com/tiptech.html
> > > or Mike Alexander's site at
> > >http://www.datapigtechnologies.com/ExcelMain.htm
>
> > > --
> > > Regards
> > > Roger Govier
>
> > > "David Lipetz" <dlip...@hotmail.com> wrote in message
> > >news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
> > >> Folks,
>
> > >> This newsgroup has been extremely helpful over the years and I am
> > >> thankful to all whom have helped me in the past. I now have a scenari=
o
> > >> that I can not even begin to solve and hope that someone can send me =
in
> > >> the right direction.
>
> > >> Our ERP system can export transactional data. I would like to be able=
 to
> > >> analyze the data on an invoice by invoice basis to determine metrics =
such
> > >> as average total invoice amount for invoices that include a specific =
part
> > >> number.
>
> > >> The ERP data extract looks something like this:
>
> > >> CustNo =A0Date =A0 =A0 =A0InvNumber =A0 =A0ItemNo =A0QTY =A0 UnitPr =
=A0 =A0ExtPr =A0 =A0 UnitCo
> > >> ExtCo
> > >> 2301 =A0 =A003/12/08 =A0 =A0100101 =A0 =A0 =A090873 =A0 =A0 =A0 3 =A0=
 =A0 =A0 =A05.00 =A0 =A0 15.00 3.00
> > >> 9.00
> > >> 9911 =A0 =A003/12/08 =A0 =A0100102 =A0 =A0 =A072FG6 =A0 =A0 =A01 =A0 =
=A0 =A075.00 =A0 =A0 75.00 42.00
> > >> 42.00
> > >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0SYSTM =A0 =A01 =A0 =A05=
00.00 =A0 =A0500.00 =A0 =A0250.00
> > >> 250.00
> > >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0AR987 =A0 =A0 =A01 =A0 =
=A0 =A020.00 =A0 =A0 20.00 10.00
> > >> 10.00
> > >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0AR990 =A0 =A0 =A01 =A0 =
=A0 =A050.00 =A0 =A0 50.00 29.00
> > >> 29.00
> > >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 =A0SW321 =A0 =A0 1 =A0 =A0=
 100.00 =A0 =A0100.00
> > >> 65.00 65.00
> > >> 3125 =A0 =A003/12/08 =A0 =A0100104 =A0 =A0 =A0ACC01 =A0 10 =A0 =A0 =
=A0 =A0 5.00 =A0 =A0 50.00 3.00
> > >> 30.00
> > >> 7865 =A0 =A003/12/08 =A0 =A0100105 =A0 =A0 =A0ACC12 =A0 =A0 2 =A0 =A0=
 =A0 19.00 =A0 =A0 38.00 11.00
> > >> 22.00
> > >> 2029 =A0 =A003/12/08 =A0 =A0100106 =A0 =A0 =A0SYSTM =A0 =A01 =A0 =A0 =
500.00 =A0 =A0500.00 =A0 =A0250.00
> > >> 250.00
> > >> 2029 =A0 =A003/12/08 =A0 =A0100106 =A0 =A0 =A0AR987 =A0 =A0 =A01 =A0 =
=A0 =A0 20.00 =A0 =A0 20.00 10.00
> > >> 10.00
>
> > >> Assume that there are thousands of records in every extract and that =
the
> > >> column headers are also the named ranges.
>
> > >> I would like a formula that would identify every "InvNumber" for a
> > >> specific "ItemNo", then be able to provide a total sum for that invoi=
ce
> > >> as well as the "CustNo".
>
> > >> Using the data set above, if the "ItemNo" criteria were set to "SYSTM=
",
> > >> the result of the formula would be:
>
> > >> CustNo =A0Date =A0 =A0 =A0InvNumber =A0 =A0ExtPr =A0 =A0 ExtCo
> > >> 1090 =A0 =A003/12/08 =A0 =A0100103 =A0 =A0 670.00 =A0 =A0354.00
> > >> 2029 =A0 =A003/12/08 =A0 =A0100106 =A0 =A0 520.00 =A0 =A0260.00
>
> > >> Completely at a loss here.
>
> > >> Thanks in advance,
>
> > >> David- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
4/8/2008 9:55:29 AM
Hi Gerry

No, its definitely not you.
I misread the OP saying he wanted the Invoice TOTAL, where Item SYSTM was 
included.
I read it as him wanting the value for SYSTM

Mea Culpa

-- 
Regards
Roger Govier

"GerryGerry" <Gerry@Gerry.anon> wrote in message 
news:qbGKj.42767$qj3.18584@newsfe20.ams2...
> It's probably me being dense, but although I got the correct layout, the 
> totals were different, (as it only totaled the product selected but not 
> for the entire invoice) i.e I got the following:
>
> CustNo  Date      InvNumber    ExtPr     ExtCo
> 1090    03/12/08    100103     500.00    250.00
> 2029    03/12/08    100106     500.00    250.00
>
> Which does not match the OP's desired table.
>
> BTW I'm using Excel 2007
>
>
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
> news:E445086E-F925-4E9F-B7AB-CCA285FA8BDD@microsoft.com...
>> Perhaps I am being extremely dense here - not an uncommon occurrence - 
>> but when I took the OP's data and Pivoted it in the manner I described, I 
>> obtained the result layout he was seeking.
>>
>> -- 
>> Regards
>> Roger Govier
>>
>> "GerryGerry" <Gerry@Gerry.anon> wrote in message 
>> news:Y0nKj.9617$Lq5.6324@newsfe18.ams2...
>>> I understand that, but what I was pointing out was that it doesn't give 
>>> the result the original poster required.
>>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
>>> news:F67D56B5-DABA-4940-9D2F-19E6A66FBECF@microsoft.com...
>>>> But that is the whole point about a Page Filter.
>>>> The totals will be just for that Item.
>>>>
>>>> -- 
>>>> Regards
>>>> Roger Govier
>>>>
>>>> "GerryGerry" <Gerry@Gerry.anon> wrote in message 
>>>> news:S9NIj.6$rk6.2@newsfet15.ams...
>>>>> Once SYSTM is selected, it filters and totals for that item only 
>>>>> messing up the ExtPr and ExtCo totals for the given invNumber
>>>>>
>>>>>
>>>>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
>>>>> news:D3E1C184-FAF0-46B5-822C-22005B6C4F1B@microsoft.com...
>>>>>> Hi David
>>>>>>
>>>>>> Create a Pivot Table
>>>>>> Place cursor in Header row>Data>Pivot Table>Finish
>>>>>> On the PT skeleton that appears on the new page
>>>>>> Drag Item No to Page area
>>>>>> Drag Cust No to Row area
>>>>>> Drag Inv Number to Row Area
>>>>>> Drag Date to Row Area
>>>>>> Drag Ext Pr to Data Area
>>>>>> Drag Ext Co to Data Area
>>>>>>
>>>>>> Drag the Data button and drop on Total to get the two data values 
>>>>>> side by side.
>>>>>> Use the dropdown on Item No to select SYSTM
>>>>>>
>>>>>> For more information on using Pivot Tables, take a look at Debra 
>>>>>> Dalgleish's site and scroll to Pivot Tables
>>>>>> http://www.contextures.com/tiptech.html
>>>>>> or Mike Alexander's site at
>>>>>> http://www.datapigtechnologies.com/ExcelMain.htm
>>>>>>
>>>>>>
>>>>>> -- 
>>>>>> Regards
>>>>>> Roger Govier
>>>>>>
>>>>>> "David Lipetz" <dlipetz@hotmail.com> wrote in message 
>>>>>> news:ORjHX0ElIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>>>>> Folks,
>>>>>>>
>>>>>>> This newsgroup has been extremely helpful over the years and I am 
>>>>>>> thankful to all whom have helped me in the past. I now have a 
>>>>>>> scenario that I can not even begin to solve and hope that someone 
>>>>>>> can send me in the right direction.
>>>>>>>
>>>>>>> Our ERP system can export transactional data. I would like to be 
>>>>>>> able to analyze the data on an invoice by invoice basis to determine 
>>>>>>> metrics such as average total invoice amount for invoices that 
>>>>>>> include a specific part number.
>>>>>>>
>>>>>>> The ERP data extract looks something like this:
>>>>>>>
>>>>>>> CustNo  Date      InvNumber    ItemNo  QTY   UnitPr    ExtPr UnitCo 
>>>>>>> ExtCo
>>>>>>> 2301    03/12/08    100101      90873       3        5.00     15.00 
>>>>>>> 3.00 9.00
>>>>>>> 9911    03/12/08    100102      72FG6      1      75.00     75.00 
>>>>>>> 42.00 42.00
>>>>>>> 1090    03/12/08    100103      SYSTM    1    500.00    500.00 
>>>>>>> 250.00 250.00
>>>>>>> 1090    03/12/08    100103      AR987      1      20.00     20.00 
>>>>>>> 10.00 10.00
>>>>>>> 1090    03/12/08    100103      AR990      1      50.00     50.00 
>>>>>>> 29.00 29.00
>>>>>>> 1090    03/12/08    100103      SW321     1     100.00    100.00 
>>>>>>> 65.00 65.00
>>>>>>> 3125    03/12/08    100104      ACC01   10         5.00     50.00 
>>>>>>> 3.00 30.00
>>>>>>> 7865    03/12/08    100105      ACC12     2       19.00     38.00 
>>>>>>> 11.00 22.00
>>>>>>> 2029    03/12/08    100106      SYSTM    1     500.00    500.00 
>>>>>>> 250.00 250.00
>>>>>>> 2029    03/12/08    100106      AR987      1       20.00     20.00 
>>>>>>> 10.00 10.00
>>>>>>>
>>>>>>>
>>>>>>> Assume that there are thousands of records in every extract and that 
>>>>>>> the column headers are also the named ranges.
>>>>>>>
>>>>>>> I would like a formula that would identify every "InvNumber" for a 
>>>>>>> specific "ItemNo", then be able to provide a total sum for that 
>>>>>>> invoice as well as the "CustNo".
>>>>>>>
>>>>>>> Using the data set above, if the "ItemNo" criteria were set to 
>>>>>>> "SYSTM", the result of the formula would be:
>>>>>>>
>>>>>>> CustNo  Date      InvNumber    ExtPr     ExtCo
>>>>>>> 1090    03/12/08    100103     670.00    354.00
>>>>>>> 2029    03/12/08    100106     520.00    260.00
>>>>>>>
>>>>>>> Completely at a loss here.
>>>>>>>
>>>>>>> Thanks in advance,
>>>>>>>
>>>>>>> David
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
> 
0
Roger
4/8/2008 1:59:05 PM
Reply:

Similar Artilces:

viewing formulas/concatenate
I'm attempting to use the concatenate formula (something that I've done before). After I enter the formula and press enter, the formula, and not the result populates the cell. I know that the formula worked because when I press the = sign, I can see that formula result. I just cannot see it in the cell. Any thoughts? Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that excel changed the format of B1 to match the format in A1. Now format D1 as Text. put ASDF in D1 put =D1 in E1 You see ...

Help with Formula #12
Hi, I'm after help with the following formula. I have a number in Cell A1, I would like a formula in Cell A2, that will put a number depending on what the number in Cell A1 is into Cell 2. If the number in Cell A1 is 1 to 20 then Cell A2 should show 1 If the number is Cell A1 is 21 to 26 then Cell A2 should show 2 and If the number is Cell A1 is 27 to 32 then Cell A2 should show 3. Many Thanks something like =if(a1>26,3,if(a1>20,2,if(a1>0,1,""))) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "grobertson" <grobertson@discussions.microsoft.c...

Moving Profiles from one computer to another
I am running Windows XP Media Edition. I frequently need to use Outlook on one computer, and then need to use it on another computer. I have two questions: 1. Can I copy my Profile settings (Contacts, Calender etc....), put them on a CDRW and bring them to the other computer which is also running the same version of Outlook? 2. If so...where do I find the file or folder with all the settings? Thanks in advance! Chaz You can move the actual mail etc - it';s stored in a *.pst at C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Outlook. However, account ...

Can Sheet name be part of formula for a cell (+ sheet name)
Worsheet name will change from template. Would like worksheet name to auto print in cell on separate worksheet. Can this be done? Hi, You can have the name being printed in the worksheet you are in for example in cell A1, enter =MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) then go to the worksheet where you want the name to be populated and enter =yoursheet name!A1 "Leslie" wrote: > Worsheet name will change from template. Would like worksheet name to auto > print in cell on separate worksheet. Can this...

One part of my border won't print
I am trying to print some invitations and the right side of my border will not print. It is on my print preview screen, but will not print. It is the same distance from the edge of the page as the other three borders. I have even tried just moving it WAY to far inside, and it still won't print. I have tried everything with no success. Please someone help. Is it the entire right side or just a portion? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Rob Drummond" <RobDrummond@discussions.microsoft.c...

Stop excel from copying data from one sheet to another
I have a workbook (actually 75 workbooks) and it has ten worksheets. In each sheet I have a subtotal at the end of each row (34 in all) and at the bottom of each column (25 in all) I have copied this formula onto each sheet. The problem I am having is that in the blank cells (of any worksheet), I enter a number and it is being transposed onto the other nine worksheets. I have no formulas in any of the cells or the other worksheets telling it to transfer the information. What I need to know is, how do i stop it from copying information from one sheet to the other without having to re...

fail to update a selected record
The form call a selected record by "sqlDataReader" then fill in textboxes by (TxtPhone.Text = reader("Dest_Addr").ToString) i made the amendments to the textboxes and click Update button strUpdate = "Update table set col1=@TxtPhone where ID='" & ID & "'" cmdUpdate = New SqlCommand(strUpdate, myConnection) MsgBox(TxtPhone.Text) cmdUpdate.Parameters.AddWithValue("@TxtPhone", "852" + TxtPhone.Text) however the textbox return to old values the record can be updated by erasi...

Service Selection Rules ERROR
When I am in the service pane and I try to schedule a new service activity using one of our users I get the error message " Service selection rules for required resourse "User name" cannot be met. To see selection rules open the record for this service". It won't let me save and apply And I am lost as to were to go from here. Please help I am still so new to this. Thanks -- Heather hi Heather You need to go to services and then click the servict activity you are trying to use and make sure the user you are trying to schedule this for exists as a resource in th...

Send to One Note
Is there anywhere to get an Send to One Note printer driver for a 64 bit machine? Tim Tim wrote: > Is there anywhere to get an Send to One Note printer driver for a 64 > bit machine? 1.) YES! Pls see: David Rasmussen: OneNote Print Driver – A 64 Bit Solution http://blogs.msdn.com/david_rasmussen/archive/2009/04/22/onenote-print-driver-a-64-bit-solution.aspx 2.) Install ON2010 from the Office 2010 Beta. The 64-bit printer is built-in there. Rainald The Office 2010 Beta has 64 bit support for the printer. For OneNote 2007, there is a workaround at http://blog...

Excel formula not working
I encounter a Excel problem, let me explain it in a examble: A1=10 B1=20 C1=2000 D1=20 E1=C1-A1-B1-C1 E1 will shows value 1950 Then Copy E1 all the column down to E10 There are not values in cells below A1, B1, C1, and D1. Cells E2~E10 should show value 0 However, they show 1950, the same value as E1. ANyone have this problem before? Hi (ignoring the error in the formula in E1) check that tools / options / calculation is set to automatic if that is not the problem please copy & paste the formula in cell E2 into your reply psot. Cheers JulieD "KC Mao" <kc@sinzon.com...

Array formula #8
I seem to be having a problem with an array formula. I have a spreadsheet with names in one column, home department numbers in column B, earning codes in column C, worked dept in column D and hours in column E. I'm trying to sum only one type of earning code for each employee for the home dept so I'm using this formula: {=sum((name=a2)*(dept=b2)*(earn_code=c2)*hours)} and all I get is #NUM. I can't figure out what I am missing. Can anyone guide me? Thanks. -- Constance Try this: =SUMPRODUCT(--(name=a2)*(dept=b2)*(earn_code=c2)*hours) Assuming the words name, dept, ear...

highlight cells to obtain total without using formula?
In previous versions of Excel, I use to be able to select a group of cells to obtain a total, without having to use a formula. The total would appear at the bottom of the window. Is this function still available in Excel2003? Do you have a visible status bar (Tools>Options>View, and see if status bar is checked)? -- HTH RP (remove nothere from the email address if mailing direct) "rdavia" <rdavia@discussions.microsoft.com> wrote in message news:98C2B407-DE54-4C0D-A6AE-89A6A50E751C@microsoft.com... > In previous versions of Excel, I use to be able to select ...

Two Computers on the same network using the one email address!
Hi, I have two networked computers sharing one email address. I have outlook 2007 and the other computer has Outlook 2003. Is there a way that both computers can share the one email address with out all mail going to one or other computer?? I'd appreciate any suggestions as mail is getting lost and mixed up. Thanks -- Val Assuming a POP3 account; Configure your account settings to leave a copy on the server. For details see; http://www.msoutlook.info/question/160 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.how...

pasting formulae to entire column in macro
Apologies to all if this is really easy! I'm a fairly new user who ha been thrown in at the deep end. I am trying to create a macro in which I will add a new column, write formula in one cell and then copy that formula to every cell in tha column. The spreadsheet in which the macro runs varies in the number of lines All I seem to be able to do is paste the formula to all 65,000 cells o to a fixed amount of cells. Can anyone help please? Thanks Coli -- Message posted from http://www.ExcelForum.com Hi use something like the following Sub insert_formulas() Dim LastRow As Long LastRo...

Is there a way to combine Word and Excel documents into one app?
Trying to use one Office app to open/edit word and excel documents from a single pane of glass...somewhat like a workbook using Office 2007 Pro No, while you can embed excel docs into word, its not really practical when editing. You could save them as PDFs... -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST...

(Publisher 2003) How do I get the two pages to print on to one pag
I need 2 pages to print on one page. I did see on a document that someone else made on my computer that the page indicators (at the bottom left; to switch between the two pages) that the folded part of the 2nd page was turned a different way, and I know that's exactly what I need to do, but I'm not sure how to. Help please? Thanks! Vampressjaden In Publisher 2000 (may be the same for other versions) File Page Setup Special fold Horizontal It will ask you if you want 4 pages - yes -- Don Vancouver, USA "Vampressjaden" <Vampressjaden@discussions.micros...

Formula Evaluate Teaser
I need to evaluate the result of a modelled cell through VBA. Can someone give me an idea on how to do this? E.g Cell A1 Formula: ="A"&LEFT(H14,1) Cell H14 Text: World Cell A1 result: AW Through VBA I need to get the result for LEFT(H14,1). This would go something like below: Sub Evaluate() Dim strForm As String strForm = ActiveCell.FormulaLocal strForm = Right(strForm, Len(strForm) - InStr(1, strForm, "&")) Debug.Print strForm End Sub How can I evaluate the strForm part of the formula to return the result "W"? Any help would be appre...

Calendar list formula or function for mon-Fri please
Hi I need to create a calendar showing ONLY the dates for Mondays to Fridays. Can anyone help please? Excel 2003 Ron Just hide the columns for Saturday and Sunday, then print. -- Regards, Fred Please reply to newsgroup, not e-mail "Ron & Joan Adams" <ronadams@paradise.net.nz> wrote in message news:42cf04d7$1@clear.net.nz... > Hi > > I need to create a calendar showing ONLY the dates for Mondays to Fridays. > Can anyone help please? > > Excel 2003 > > Ron > Perhaps a start: Array Calendar: http://j-walk.com/ss/excel/files/general.h...

show text after formulas
I have some formulas with text added to the result. For example, I might have a result of: 4.9¢/kWh In the formula cell, I set the numeric format to number, one digit after decimal. How do I retain the format when I have the numeric set to show commas? When I want to show a result of: 2,345,899 kWh Excel gives me a result of: 2345899 kWH. Why is it ignoring my format when I introduce text? A formula example when this happens might be: =ROUND(((G40/H45)*D19*100 ),0)&"kWh" help! this is driving me crazy!! I want to show units in the same cell, but these n...

Entering formula
OK, first let me say that I am a complete idiot in the ways of Excel. That said, I need help entering a formula. Step by step would be nice or direction to a tutorial. ( I have used the help file to no avail). What I want is an Excel backup to what I use in the field on a TI-86 or an HP 33s. I can enter and store the following on the handhelds but cannot seem to get it right on Excel. L-DxDx.0000006= or (LxD-D2)x.0000006= Of course L and D are variables and the .0000006 remains static. Thanks to anyone that can lead me in the right direction. All Excel formulas begin with the = sig...

Why does my advance filter only show one record?
Hi - I have set-up an advance filter but it only shows one record rather than all records that meet the filter criteria. The unique box is not checked Perhaps there's a blank row near the top of the table (it may be hidden). If you delete the blank row, the filter may work correctly. Kyri wrote: > Hi - I have set-up an advance filter but it only shows one record rather than > all records that meet the filter criteria. The unique box is not checked -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

VBA: Column Select then Data Select then return to cell A1
How do you write in VBA:- to select a column then select the range of data held in the column (say B1) then return to Cell a1. The data in the column can change in the workbook as it is updated regulary, so the range of data in the column varies constantly. There are column headings so the range in column B1 must start at B2. Many thanks -- James Hi, It's not entirely clear what you want but maybe this which copies the used range of column B and pastes it into A2 Sub marine() Dim LastRow As Long Set sHt = Sheets("Sheet1")'Change to suit LastRow = sH...

Would like an explanation of a formula:
=("1/1/"&(IF(LEFT(DQ10,4)*1<20,2000,1900)+LEFT(DQ10,4)))+MOD(DQ10,1000)-1 The other day I needed a formula that would change a Julian date to a calendar date, e.g., 2006031 to 01/31/06. I googled the question and found the answer, for which I am grateful, in a formula provided by Joseph Rubins Excel Tips. It worked like a charm and I was wondering if someone would take the time to explain exactly how it works. Thanks -- Leo First, it didn't work for me. With 2006031 in DQ10, I got 01/31/3096 as the result. It's trying to build a date string 1/1/someyear +...

If "Hide from exchange address list" is selected does this prevent you from resolving this contact when attempting to configure Outlook?
I had a user leave the our firm. Instead of disabling the account I removed the user form all distlists and hide the contact in the GAL. Today I needed to check something in the old mailbox. I tried adding this mailbox through Tools>email accounts>view or change existing email accounts... But I was not able to resolve the name. Is this because the name is hidden? If this is the case, what is the best way to continue to administer a mailbox once a user leaves. I do not want to disable the account yet. The boss still wants an "out of office auto-reply" for this user for th...

Need some help with a formula...thanks in advance
I have a tab called Record. Here are the cells it contains: A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't ...