remove duplicate rows in excel

[Anirban Majumdar]

Can anyone please help me the way to remove second duplicate rows in excel ?
For example: In below need to remove rows with red font.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Details[/TD]
[TD="class: xl65, width: 64"]Num#[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]AAA[/TD]
[TD="class: xl66, align: right"]62600173[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]AAAB[/TD]
[TD="class: xl69, align: right"]62600173[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]CCC[/TD]
[TD="class: xl66, align: right"]62600021[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]CCCB[/TD]
[TD="class: xl69, align: right"]62600021[/TD]
[/TR]
</tbody>[/TABLE]

Anirban

 

[jarjarbingie]

Can anyone please help me the way to remove second duplicate rows in excel ?
For example: In below need to remove rows with red font.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Details[/TD]
[TD="class: xl65, width: 64"]Num#[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]AAA[/TD]
[TD="class: xl66, align: right"]62600173[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]AAAB[/TD]
[TD="class: xl69, align: right"]62600173[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]CCC[/TD]
[TD="class: xl66, align: right"]62600021[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]CCCB[/TD]
[TD="class: xl69, align: right"]62600021[/TD]
[/TR]
</tbody>[/TABLE]

Anirban

do you need a dynamic solution? if not, highlight the table, go to Data -> Remove Duplicates, check My data has headers, check Num# and press OK.

 

[Trebor76]

Hi Anirban,

Welcome to MrExcell!!

Assuming the numbers are in Col. B and the data is how you show it here, try this while on the sheet with the data (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = lngLastRow To 2 Step -1
        If Range("B" & lngMyRow) = Range("B" & lngMyRow - 1) Then
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Anirban Majumdar]

do you need a dynamic solution? if not, highlight the table, go to Data -> Remove Duplicates, check My data has headers, check Num# and press OK.

Yes , i need dynamic solution. Thanks

 

[jarjarbingie]

Yes , i need dynamic solution. Thanks

see if the macro above works for you.. with formulas, we can just reproduce the filtered list in another sheet..

 

[Anirban Majumdar]

I tried the given Macro. But unfortunately it didn't worked.

 

[Trebor76]

That's odd - it worked for me

 

[Mike Szczesny]

Can you use something like this? =IFERROR(INDEX($B$2:$B$5,MATCH(0,COUNTIF($D$1:$D1,$B$2:$B$5),0))," "). Place Unique in D1. Use Cntrl+Shift+Enter. Copy down.

details Num Unique
AAA 62600173 62600173
AAAB 62600173 62600021
CCC 62600021
CCCB 62600021