Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    sum based on matches (2002)

    I have a workbook of someone's that has a master wine list in one sheet. Subsequent sheets are based on the vintage (one vintage per sheet) from the master (and only a few cols of from the master). These "vintage" sheets are filtered based on one col (non-blanks). I suspect I need a macro (VB) that would enable me to do the following (because I can't for the life of me figure out how in "traditional" Excel).

    There are three criteria (cols) in the vintage sheets that would make lines match other lines (e.g., if cols A, C and D match) for which I then want only a single entry for this item and, on top of that, I want the total of column B for those items.

    A better example would be if a given vintage sheet shows several wines of the same name (col C), same year (D) and same bottle size, (A), total the number of bottles ([img]/forums/images/smilies/cool.gif[/img] and show this as only a single line in the filtered sheet(s).

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum based on matches (2002)

    I'm sure you've explained that really well but I'm afraid I'm having trouble following! Please could you post an example workbook (using dummy data if you like)?
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sample file

    <P ID="nt"><font size=-1>(No Text)</font>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sample file

    Try a pivot table:
    Select an item in row 1
    Data - Pivot table and chart report
    [Next][Next][Layout]
    Place as ROW fields
    Unit, WIne, VIntage
    Place in DATA field
    Qty(437)
    [Dbl-click the "Count of.." and select "SUM"
    [OK][OK]
    Select wheer you want the table
    [OK]

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sample file

    Thanks, Steve...
    While the pivot table works...the end user (owner of the wine) hates look of the pivot table. I was never fond of them either (maybe I just don't fully understand or appreciate what you can do with them). It shows all of the sub-total lines, etc. For the end user, it's too cluttered, he says.

    He was hoping to see just the summation of quantity for those that are the same (i.e., a single entry with the right total).

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sample file

    I'm trying to make the pivot table look acceptable for this person.
    I guess I could remove the subtotals which will remove some of the "clutter".
    How would I remove the "0" total that appears at the top (I suspect because of the fltering and a set of items that are blank??)

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

    Re: sample file

    Any chance you could use Access? It would be very easy to create a query and a report that display what you want.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sample file

    Unfortunately, Access isn't an option.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sample file

    The pivot table does the trick...once I remove the subtotals (not a major issue)...not sure how to easily remove the 0 total at the top, however.
    There are about 20 sheets and to remove that total line at the top on each one would be a pain...could be done, just not convenient.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sample file

    I'd create a pivot table based on the table in the Full inventory sheet, with Sorted By as Page field. Use the Show Pages item on the Pivot Table dropdown on the Pivot Table toolbar to create individual sheets for Ausone, Beaune, etc.

  11. #11
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sample file

    Show Pages - that's new to me. Another one for the toolkit! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    Thanks Hans
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sample file

    You can eliminate the zero at the top by not having blank rows in the dataset. It is summing up the blank entries.

    Steve

Posting Permissions

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