+ Reply to Thread
Results 1 to 11 of 11

Advanced filter not showing unique records.

  1. 07-05-2015, 02:16 AM #1
    bubai
    bubai is offline
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    43

    Advanced filter not showing unique records.

    In my attached sheet, I have a column with Cheque number appearing multiple times with different Amounts. I tried to show the unique values in a separate column so that i could do the summation of the Amounts for each cheque. I used Advanced filter with `Copy to another Location' & `Unique records only' option turned on, but Cheque Summary column showing the first Cheque twice. In the adjacent column i am having a Sumif formula to get the summation and the first Cheque (a) is showing two different sum at the two separate apperances.
    Why is this being shown more than once? Or is there any better way to show unique records?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-05-2015, 02:49 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Advanced filter not showing unique records.

    1. The second appearrance of a. You selected different starting points fo the list and sort criteria. Both should have been row 1.

    2. Totals. the formula was changing as you moved down the column; so a different formula was being applied at the second occurrence of a.. For SUMIF and SUMIFS, just select the entire column and use =SUMIF(A:A,D2,B:B) instead.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 07-05-2015, 02:57 AM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Advanced filter not showing unique records.

    Edit Failed to refresh before posting.

    You are getting two different amounts because of the relative addressing established in the first row of Accumulated Amount. It is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    By the time you get to the second occurrence of cheque a the relative address has changed and no longer includes the earlier values in column B. I.e.at the second occurrence of cheque a (row 8) the formula then reads:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Your criteria is a different range and so is your sum range. Try instead:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    You will get the same results for both cheque a entries.

    As far as getting unique numbers for each of your cheques I've never used the method you describe, but if you copy / paste the cheque numbers from column A into column D and then while still selected click Data / Remove duplicates you will have unique entries.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  4. 07-05-2015, 03:02 AM #4
    bubai
    bubai is offline
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Advanced filter not showing unique records.

    Glenn
    I understand the second answer the problem with the Sumif function. But waht about the second appearance of (a)? It was used with an Advanced Filter. How to rectify that?
    Reply With QuoteRegister To Reply
  5. 07-05-2015, 03:04 AM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Advanced filter not showing unique records.

    Quote Originally Posted by bubai View Post
    Glenn
    I understand the second answer the problem with the Sumif function. But waht about the second appearance of (a)? It was used with an Advanced Filter. How to rectify that?
    Did you read the last paragraph of my post above?
    Reply With QuoteRegister To Reply
  6. 07-05-2015, 03:05 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Advanced filter not showing unique records.

    Double posted.
    Reply With QuoteRegister To Reply
  7. 07-05-2015, 04:03 AM #7
    bubai
    bubai is offline
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Advanced filter not showing unique records.

    All right Flame
    I guess yours is the better method. But on the net I have found only the method with the Advanced Filter.
    Cheers to all the mates that have helped me!
    Reply With QuoteRegister To Reply
  8. 07-05-2015, 04:39 AM #8
    bubai
    bubai is offline
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Advanced filter not showing unique records.

    One more question.
    Is there a way to make the column D dynamic so that when there is any change in the original column A that will be reflected in Column D?
    Reply With QuoteRegister To Reply
  9. 07-05-2015, 05:46 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Advanced filter not showing unique records.

    Yes.
    In the attached file I changed the Named Range “Criteria” to a Dynamic Named Range. It will shrink and grow as data is entered in column A. Its current upper boundary is row 10,000. Edit that figure in Name Manager should your needs exceed that number. The formula is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Then array-enter this formula in cell D2 and fill / copy down until you get blanks.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If you are not familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    This will dynamically return unique cheques from column A.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 07-06-2015, 02:18 AM #10
    bubai
    bubai is offline
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Advanced filter not showing unique records.

    One hell of a formula! Doing the unique records automatically. Excellent stuff! Rating it's solved.
    Cheers mate!!
    Reply With QuoteRegister To Reply
  11. 07-06-2015, 02:34 AM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Advanced filter not showing unique records.

    Happy to hear it. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Advanced Filter and Unique Records Help
    By Jericho Knuckles in forum Excel General
    Replies: 2
    Last Post: 04-06-2011, 06:44 PM
  2. Excel 2007 : Loop Through Advanced Filter Unique Records
    By Wrecking_Crew in forum Excel General
    Replies: 5
    Last Post: 03-21-2010, 01:20 PM
  3. Advanced Filter - Unique Records Only
    By pschultz in forum Excel General
    Replies: 0
    Last Post: 05-15-2007, 05:55 PM
  4. Advanced filter to show unique records
    By ResulG in forum Excel General
    Replies: 3
    Last Post: 12-04-2006, 09:06 AM
  5. Error using Advanced Filter Unique Records
    By headly in forum Excel General
    Replies: 5
    Last Post: 05-08-2006, 08:40 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1