+ Reply to Thread
Results 1 to 4 of 4

Sort & Filter Unique items

  1. #1
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Sort & Filter Unique items

    I thought it would be easy to run the Macro recorder.
    First record a Sort. Then record the AdvancedFilter on Unique items.
    Triggered both Macros by a Worksheet Change event.

    Problems:
    1. Sort Only works with a 1 (number or text) in the first position - under INPUT (B5:B16)
    2. FilterUnique don't filter the above 1, leaving two 1's - under OUTPUT (C5)

    What am I missing?
    Ola

    Example file attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Contributor colofnature's Avatar
    Join Date
    05-11-2006
    Location
    -
    MS-Off Ver
    -
    Posts
    301
    Just guessing from inspecting the code, but you might try changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and replacing the FilterUnique sub with
    Please Login or Register  to view this content.
    which I think might work. Maybe...
    Col
    If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Sort & UniqueFilter

    For the record, problem solved!
    The code - as usual - may not be the nicest but it works.

    The problem was the AdvancedFilter.
    Advanced filter requires that the Output Header is exactly the same as the Input Header (and part of the range).
    When the Input range has several headers, the Output headers decide what goes where.
    I missed that but Contextures - as usual - gave me the sparkplug

    Next, insight. If the output range only includes the Header cell, instead of the whole range,
    everything else in that column, will be erased. Meaning if (C1) instead of (C1:C12) --> all text below C12 will be erased.

    colofnature: thanks for the reply. I had already tested to include Sort headers. That was part of the confusion
    And... since the problem was with AdvancedFilter, Sort works both with and without header, but I prefere with.

    Still learning
    Ola


    Encl. file: Sort & UniqueFilter
    Attached Files Attached Files
    Last edited by olasa; 08-02-2008 at 06:19 PM.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Sum unique items

    Here is another example, this will paste the unique items into another sheet and will use sumif to get the total values.
    SumifUniqueItems.xls

+ 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