Results 1 to 4 of 4

Thread: Hide duplicate rows from filtered data

  1. #1

    Hide duplicate rows from filtered data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a problem and i have to explain you with my exact data...

    my excel sheet is something like that:

    Column A (Name), Column B (MSISDN), Column C (Date) Column D (time) and Column E (Location)..

    Name MSISDN Date Time Location
    STO Poultry (Sheikhupura) Dr. Muzaffar Ali +923362550536 01/9/2011 12:37 PM Ravi Ryan Mouza Goband Tehsil Farozewala & District Muredke Sheikhupura Ravi Rayan
    STO Poultry (Sheikhupura) Dr. Muzaffar Ali +923362550536 02/9/2011 1:37 PM New Banbosan road, Lari Adda, Nawa Lahore, Lahore Cantt.
    STO Poultry (Sheikhupura) Dr. Muzaffar Ali +923362550536 03/9/2011 1:37 PM Ravi Ryan Mouza Goband Tehsil Farozewala & District Muredke Sheikhupura Ravi Rayan
    STO Poultry (Sheikhupura) Dr. Muzaffar Ali +923362550536 04/9/2011 2:37 PM Ravi Ryan Mouza Goband Tehsil Farozewala & District Muredke Sheikhupura Ravi Rayan
    STO Poultry (Sheikhupura) Dr. Muzaffar Ali +923362550536 05/9/2011 2:37 PM Muridkey Road, Ferozwala, rasheed Garh, Lahore
    SPO Farm (Lahore) Zubair Ali +923362550537 01/9/2011 12:37 PM Jam Saqid Road, Near new thana Garden Town Lahore

    Now First i filter by SPO Name (Column A) then Date (Column C) and then Hide duplicate locations (duplicate Rows in Column E)..

    can anybody help me out?
    Attached Files Attached Files

  2. #2
    Seeker jpr73's Avatar
    Join Date
    Apr 2011
    Location
    France
    Posts
    15
    Articles
    0
    Hay,
    The line 1106 is not correct.
    I suggest to make a Pivotable for an easy filter
    http://www.cijoint.fr/cjlink.php?fil...cij9UPspJ1.xls
    Best regards

  3. #3

    Hide duplicate rows from filtered data

    Hi,

    I suggest you use advanced filter to show only unique values for column C and then copy paste the visible cells to another sheet.I hope this would help you.

    Cheers...

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    The attached workbook has code to create an advanced filter for Unique values only, for the 2 criteria that you select.
    Sheet Lists has a list of Unique Names and dates that are created as part of the macro, and this provided the data for the 2 dropdown lists on sheet Extracted.

    Some Dynamic Ranges have been set up, which are
    myData =LHR!$A$5:INDEX(LHR!$J:$J,COUNTA(LHR!$A:$A)+4)
    Alldates =LHR!$D$5:INDEX(LHR!$D:$D,COUNTA(LHR!$D:$D)+4)
    Allnames =LHR!$A$5:INDEX(LHR!$A:$A,COUNTA(LHR!$A:$A)+4)
    Dates =Lists!$B$2:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B))
    Name =Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))

    and then the code activated by a button on sheet Extracted is
    Code:
    Sub FilterData()
        Dim wss As Worksheet, wsd As Worksheet, wsl As Worksheet
    
    
        Set wss = Sheets("LHR")
        Set wsd = Sheets("Extracted")
        Set wsl = Sheets("Lists")
    
    
        wss.Range("Allnames").AdvancedFilter Action:=xlFilterCopy, _
                                             CopyToRange:=wsl.Range("A1"), Unique:=True
    
    
        wss.Range("Alldates").AdvancedFilter Action:=xlFilterCopy, _
                                             CopyToRange:=wsl.Range("B1"), Unique:=True
    
    
        wss.Range("myData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
                                                                 :=wsd.Range("A1:B2"), CopyToRange:=wsd.Range("A5:F5"), Unique:=True
        
        Columns("C:H").EntireColumn.AutoFit
        Range("A2").Select
    End Sub
    Please see attached workbook
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

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