How to check for duplicate on specific column and more

[Adrian Low]

Hi, Im trying to detect duplicates on column("G") of my input workbook and by using lastrow of its data at column("E") to merge upwards by using & "" & after which it will delete the entireRow and this process continue until there are no more duplicates.


I tried and also look up for many codes including delete and duplicates but I am still having trouble.







Dim myCell As Range, myRow As Integer, myRange As Range, myCol As Integer, X As Integer

'Count number column

Set wsInput = Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112")

myCol = Range(Cells(3, 7), Cells(3, 7).End(xlDown)).Count

'Loop each column to check duplicate values & highlight them.

For X = 3 To myRow
Set myRange = Range(Cells(2, X), Cells(myRow, X))

For Each myCell In myRange
If Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112").CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3

End If
Next
Next


' allow values at Column"E" to merge upwards and delete all duplicate and its row (missing)



I have no clue how to delete after copying data on top of the column. Someone please help.


Many Thanks,
Adrian

 

[MickG]

Try this to remove entire rows based duplicates in "G" and Concatenate values in "E".

[COLOR="Navy"]Sub[/COLOR] MG06May15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("G3", Range("G" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, -2)
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value).Value = .Item(Dn.Value).Value & " " & Dn.Offset(, -2).Value
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick