Hide duplicate rows from filtered data

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

Prathap

New member
Joined
Mar 27, 2012
Messages
1
Reaction score
0
Points
0
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...
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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
Top