|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I receive monthly sales reports in Excel 2002, SP3.
Column A lists customers, B is City, C is State, and so on, thru Column J.
Each transaction is reported in a separate row,so in a given report, the
same customer may have several rows, often identical ones (if they purchase
the same quantity of the same part number at the same price).
I wish to create a "Year-to-Date" workbook or worksheet, which combines
monthly reports. I want all the listings for "ABC Company" to be grouped
together in the Customer column. I don't care how they are sorted within
that grouping; alpha by city, then numerically by part number would be my
choice, if given one.
Is there a way to create a report that automatically extracts"ABC Company"
from each monthly report and lumps them together on a YTD report? Note that
new customers are added every month, and some months "ABC" has no activity.
I could then get greedy, and ask for a way to combine (on the YTD report)
transactions of identical part numbers. So if "ABC Company" in Boston
purchased part number "1234" several times during the month and during the
year, the YTD report would extract and combine all these transactions
together in a YTD quantity. "ABC" in New York would have a unique entry,
even if they purchased p/n 1234 too. It'd be neat if I could show how many
1234's "ABC" bought YTD, regardless of location, and even how many 1234's
were purchased by all customers. Is that too much to ask?
Bernie
|
|
Your last paragraph is only a matter of sorting the data to give you what
you want.
Yes, there is a way to build the YTD data like you say. It would take VBA.
If you wish, send me your file or an example of your file. Clearly show the
layout of your monthly reports (column headers). For instance, is each
month on a separate sheet? If so, what are the sheet names? Also provide
the proposed layout of the YTD sheet and its sheet name. I need just the
layouts, not actual company names and addresses, etc. My email address is
ottokmnop[ at ]comcast.net. Remove the "nop" from this address. HTH Otto
"baltobernie" <bernieSPAMNOT[ at ]pennstar.com> wrote in message
news:L6pHg.11313$St4.4851[ at ]trnddc01...
[Quoted Text] >I receive monthly sales reports in Excel 2002, SP3.
>
> Column A lists customers, B is City, C is State, and so on, thru Column J.
>
> Each transaction is reported in a separate row,so in a given report, the
> same customer may have several rows, often identical ones (if they
> purchase the same quantity of the same part number at the same price).
>
> I wish to create a "Year-to-Date" workbook or worksheet, which combines
> monthly reports. I want all the listings for "ABC Company" to be grouped
> together in the Customer column. I don't care how they are sorted within
> that grouping; alpha by city, then numerically by part number would be my
> choice, if given one.
>
> Is there a way to create a report that automatically extracts"ABC Company"
> from each monthly report and lumps them together on a YTD report? Note
> that new customers are added every month, and some months "ABC" has no
> activity.
>
> I could then get greedy, and ask for a way to combine (on the YTD report)
> transactions of identical part numbers. So if "ABC Company" in Boston
> purchased part number "1234" several times during the month and during the
> year, the YTD report would extract and combine all these transactions
> together in a YTD quantity. "ABC" in New York would have a unique entry,
> even if they purchased p/n 1234 too. It'd be neat if I could show how
> many 1234's "ABC" bought YTD, regardless of location, and even how many
> 1234's were purchased by all customers. Is that too much to ask?
>
>
> Bernie
>
|
|
Will do, and if you're ever in Baltimore, I'll buy you lunch.
Bernie
"Otto Moehrbach" <ottokmnop[ at ]comcast.net> wrote in message
news:eC4j0I9xGHA.4732[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Your last paragraph is only a matter of sorting the data to give you what
> you want.
> Yes, there is a way to build the YTD data like you say. It would take
> VBA. If you wish, send me your file or an example of your file. Clearly
> show the layout of your monthly reports (column headers). For instance,
> is each month on a separate sheet? If so, what are the sheet names? Also
> provide the proposed layout of the YTD sheet and its sheet name. I need
> just the layouts, not actual company names and addresses, etc. My email
> address is ottokmnop[ at ]comcast.net. Remove the "nop" from this address.
> HTH Otto
> "baltobernie" <bernieSPAMNOT[ at ]pennstar.com> wrote in message
> news:L6pHg.11313$St4.4851[ at ]trnddc01...
>>I receive monthly sales reports in Excel 2002, SP3.
>>
>> Column A lists customers, B is City, C is State, and so on, thru Column
>> J.
>>
>> Each transaction is reported in a separate row,so in a given report, the
>> same customer may have several rows, often identical ones (if they
>> purchase the same quantity of the same part number at the same price).
>>
>> I wish to create a "Year-to-Date" workbook or worksheet, which combines
>> monthly reports. I want all the listings for "ABC Company" to be grouped
>> together in the Customer column. I don't care how they are sorted within
>> that grouping; alpha by city, then numerically by part number would be my
>> choice, if given one.
>>
>> Is there a way to create a report that automatically extracts"ABC
>> Company" from each monthly report and lumps them together on a YTD
>> report? Note that new customers are added every month, and some months
>> "ABC" has no activity.
>>
>> I could then get greedy, and ask for a way to combine (on the YTD report)
>> transactions of identical part numbers. So if "ABC Company" in Boston
>> purchased part number "1234" several times during the month and during
>> the year, the YTD report would extract and combine all these transactions
>> together in a YTD quantity. "ABC" in New York would have a unique entry,
>> even if they purchased p/n 1234 too. It'd be neat if I could show how
>> many 1234's "ABC" bought YTD, regardless of location, and even how many
>> 1234's were purchased by all customers. Is that too much to ask?
>>
>>
>> Bernie
>>
>
>
|
|
|