Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Scenario: On Worksheets 1 through 12 I have listed in column 3 receipts from various suppliers, listed by their name in column 1, and by their respective contract numbers in column 2. The number of receipts varies widely by month, ranging anywhere from 150 to more than 300. The same supplier may make more than one delivery in a given month. What I would like to do is on a Summary Worksheet, list each vendor (only once) that I have received product from over the 12 months in column 1, their respective contract number in column 2, and the total receipts from them for the year in column 3. In addition, I would like to separate the receipts from the vendors by month in columns 4-15. Any ideas?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It would be better to use a single data table on one worksheet, with an extra column that specifies the date or month.
    You can then create a pivot table to summarize the data.

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Give this a try
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='mdmackillop' post='772285' date='25-Apr-2009 21:23']Give this a try[/quote]
    Hi,

    Is your solution specific to Excel 2007? In Excel 2002, Sort is not a method of the Worksheet object, only of the Range object, so I get an error message on

    With ActiveWorkbook.Worksheets("Summary").Sort

  5. #5
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='772286' date='25-Apr-2009 15:32']Hi,

    Is your solution specific to Excel 2007? In Excel 2002, Sort is not a method of the Worksheet object, only of the Range object, so I get an error message on

    With ActiveWorkbook.Worksheets("Summary").Sort[/quote]

    Created in 2007. I'll make up a 2003 version.

  6. #6
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    2003 Version
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks, that works!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='mdmackillop' post='772295' date='25-Apr-2009 16:27']2003 Version[/quote]

    Thanks MD. I will look at it tomorrow, and let you know.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='mdmackillop' post='772295' date='25-Apr-2009 16:27']2003 Version[/quote]

    MD,
    Thanks again for your help. Here is a sample file. I have added a couple of new "twists"; namely, two descriptions. What I am looking for is the following: on Summary Worksheet 1, a listing of each supplier (and again, they could be as few as 100 in one month and as many as >350 in another month) and respective contract number, but only once, and a summation of their deliveries (my receipts) by month. On Summary worksheet 2, I would like the same thing as on Summary Worksheet 1, but itemized by month by the combination of the product description. By that I mean that if Supplier A delivered 100 Red Wrenches in January, 250 Blue Wrenches in February, and 900 Gold Computers in March, each one would be listed in Summary Worksheet 2.
    Sorry to be so confusing.
    BTW, I am a 2003 user!
    Thanks again for all of your help!
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This exceeds what I would classify as "Help" but happy to look at your proposed solutions if you have difficulties.
    I suggest you look at putting all data on one sheet with an added date colum, and then use either SumProduct or Pivot Tables (as previously suggested) to analyse the data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •