+ Reply to Thread
Results 1 to 2 of 2

Filter a Column by Another Columns Value (Grouped)

  1. #1
    Registered User
    Join Date
    07-28-2008
    Location
    Gloucester, England
    Posts
    2

    Filter a Column by Another Columns Value (Grouped)

    I have Column A (QuoteNo) and column B (QuoteSubNo). There can be many QuoteSubNo values to 1 QuoteNo, but I just want to see the latest.

    So, how do I filter QuoteSubNo on the highest number, based on a grouping of QuoteNo.

    A B
    1 1
    1 2
    1 3
    2 1
    2 2
    2 3
    2 4

    So here I would just want to see QuoteSubNo's 3, 3, 4.

    Thanks in advance.
    "Let's be open minded, but not so open minded that our brains fall out." - Richard Dawkins

  2. #2
    Registered User
    Join Date
    07-28-2008
    Location
    Gloucester, England
    Posts
    2

    Sort Cells and VLookup

    I've found a way. Here's what I've done in-case anyone else has the same problem:

    Sorted the columns by:
    A (QuoteNo) Desc
    B (QuoteSubNo) Asc

    In a blank column (C) I added a VLookup formula to match the QuoteNo and return the QuoteSubNo. Because VLookup works top to bottom it always returns the highest QuoteSubNo - because of the sort earlier.

    In another blank column (D) I added a simple boolean formula to tell me whether the VLookup result matches the QuoteSubNo. Example, in cell D2 I put "=C2=B2".

    Finally filter column D to show only TRUE.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1