+ Reply to Thread
Results 1 to 6 of 6

Return a filtered unique records without no blanks and duplicates.

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    82

    Return a filtered unique records without no blanks and duplicates.

    Hi,


    Required a formula help (office 365/Old version both) for summarize a filtered unique records with no blanks and no duplicates, as based on data contains in column (A to G).


    sample workbook attached.



    Thanks for the help.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Return a filtered unique records without no blanks and duplicates.

    Try in I3:

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Drag down and accross

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,776

    Re: Return a filtered unique records without no blanks and duplicates.

    Excel 2010 + check duplicate for Consumer, BS Cont, Activity
    I2:Oxx

    =IFERROR(INDEX(A$3:A$19,AGGREGATE(15,6,ROW($A$3:$A$19)/(MATCH($A$3:$A$19&$B$3:$B$19&$C$3:$C$19,$A$3:$A$19&$B$3:$B$19&$C$3:$C$19,)=ROW($A$3:$A$19)-ROW($A$2))/($A$3:$A$19>0),ROWS(I$3:I3))-ROW($A$2)),"")

    MS365
    =UNIQUE(FILTER(A3:G19,A3:A19>0))

    Power Query 2010+

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Return a filtered unique records without no blanks and duplicates.

    If you are really using Excel in Office 365, you should have FILTER and UNIQUE functions. If so,

    I3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which should spill into I3:M10 given your sample data. No blank lines, and distinct COMBINATIONS of values from columns A to E.
    N3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which should spill into N3:N10. Select N3:N10, copy, move to O3, and paste.

    If you need this also for older versions of Excel, then it's rather more complicated. It also requires more cells per result.

    Q3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Q4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill Q4 down until it returns #N/A.

    R3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill R3 down into the same rows as the table in columns A to G, so given your sample data, into R4:R19.

    The formulas in column Q from cell Q3 down are the row indices for the distinct columns A to E records in columns A to G.

    I3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill I3 right into J3:M3. Then select I3:M3 and fill down until the formulas return #N/A.

    N3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Fill N3 right into O3. Select N3:O3 and fill down to match the valid records in columns I to M.

    Or you could use a pivot table.
    Last edited by hrlngrv; 10-21-2020 at 03:42 AM. Reason: removing unnecessary Q1:Q2 formulas

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Return a filtered unique records without no blanks and duplicates.

    Picky: A3:A19>0 would be true for cells in A3:A19 which evaluated to "" rather than blank since Excel treats ALL text as greater than ALL numbers.

  6. #6
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    82

    Re: Return a filtered unique records without no blanks and duplicates.

    Thanks for all for your support and different ways of solutions.

    Bo_Ry's excellent 3 way solution(Old versions/365/Power query).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count Unique Filtered Records
    By BobZZ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2018, 04:42 PM
  2. Remove Duplicates so you only see unique records
    By CandaceSteinberg in forum Excel General
    Replies: 14
    Last Post: 10-12-2016, 09:03 PM
  3. Create a list that contains duplicates and unique names and no blanks
    By Whard42 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-24-2015, 08:43 AM
  4. Replies: 5
    Last Post: 04-30-2013, 07:42 PM
  5. Excel 2007 - count unique records in Filtered Table
    By redbrad0 in forum Excel General
    Replies: 0
    Last Post: 05-26-2010, 08:20 PM
  6. Count unique records in a filtered range
    By jiminic in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-24-2009, 02:44 AM
  7. Deleting Duplicates, All records unique
    By mirdonamy in forum Excel General
    Replies: 7
    Last Post: 01-11-2006, 06:10 PM

Tags for this Thread

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