Hide duplicate rows from filtered data

A

Arshmaan

New member
Joined
Oct 20, 2011
Messages
10
Reaction score
0
Points
0
Location
Pakistan
Excel Version(s)
2007, 2016
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
MSISDNDateTime Location
STO Poultry (Sheikhupura) Dr. Muzaffar Ali+92336255053601/9/2011
12:37 PMRavi Ryan Mouza Goband Tehsil Farozewala & District Muredke Sheikhupura Ravi Rayan
STO Poultry (Sheikhupura) Dr. Muzaffar Ali+92336255053602/9/2011
1:37 PMNew Banbosan road, Lari Adda, Nawa Lahore, Lahore Cantt.
STO Poultry (Sheikhupura) Dr. Muzaffar Ali+92336255053603/9/2011
1:37 PMRavi Ryan Mouza Goband Tehsil Farozewala & District Muredke Sheikhupura Ravi Rayan
STO Poultry (Sheikhupura) Dr. Muzaffar Ali+92336255053604/9/2011
2:37 PMRavi Ryan Mouza Goband Tehsil Farozewala & District Muredke Sheikhupura Ravi Rayan
STO Poultry (Sheikhupura) Dr. Muzaffar Ali
+923362550536
05/9/2011
2:37 PMMuridkey 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?
 

Attachments

  • Sep-11_Ufone Tracking_SPOs.xls
    341.5 KB · Views: 18
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...
 
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
 

Attachments

  • Sep-11_Ufone Tracking_SPOs.xls
    366.5 KB · Views: 12
You must log in or register to reply here.
Back
Top