Hide duplicate rows from filtered data

[Arshmaan]

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?

[jpr73]

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

[Prathap]

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]

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

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