Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    advanced filtering & obtaining unique items (Excel 2002 SP2)

    Hi,

    I've just started using advanced filtering and was wondering if there is a way to obtain unique items with a sum of a dollar field?
    I have a data set of over 20,000 lines. Each line is a unique payment of various types of moneys (eg mdeical costs, travel expenses). The first field in this data set is for claim number. One claim can have many payments attached to it. I want to be able to determine which claims have less than $100. in payments (in most cases would only be one payment attached to the claim). If I filter out just the payments less than $100., then pivot the results, as CLAIM NUMBER & PAYMENTS then the payments in most case exceed $100. as one claim may have 10 payments each less than $100.

    Does anyone know of a method of advanced filtering where I could specify that I only want the claim numbers where the sum of the payments for each claim is less than $100. ?

    Thanks for any assistance.

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

    Re: advanced filtering & obtaining unique items (Excel 2002 SP2)

    You could use an extra column that calculates the total per claim number, and use that in the criteria. See attached demo.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: advanced filtering & obtaining unique items (Excel 2002 SP2)

    Thanks Hans,
    Your solution will work fine. It never would have occurred to me to approach it that way. That's one of the great things about this forum, for people like me who are the only numbers person in their area, having access to other people who can suggest different ways to look at a problem, or offer potential solutions is a great help.

Posting Permissions

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