How to find duplicate value for multiple columns simultaneously

[ryan8200]

Hello all Excel experts and members,

I would like to find duplicate value for big data simultaneously. However, it would be time consuming and ineffective if I do it column by column.

I hope some of the Excel expert can give me some advice. Below are portion of the sample data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day3 [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]25[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]25[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


I would be expecting the following outcome:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day 1 [/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[/TR]
[TR]
[TD]Duplicate [/TD]
[TD]Duplicate [/TD]
[TD]Unique [/TD]
[/TR]
[TR]
[TD]Duplicate [/TD]
[TD]Duplicate [/TD]
[TD]Duplicate [/TD]
[/TR]
[TR]
[TD]Duplicate [/TD]
[TD]Duplicate [/TD]
[TD]Unique [/TD]
[/TR]
[TR]
[TD]Duplicate [/TD]
[TD]Unique [/TD]
[TD]Duplicate [/TD]
[/TR]
</tbody>[/TABLE]

Many thanks.

 

[nemmi69]

How do you want this indicated? Colour the cells?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Jul34
[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]
[COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 1 To Rng.Columns.Count
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Columns(n).Cells
      [COLOR="Navy"]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
            .Item(Dn.Value).Interior.Color = vbYellow
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
    .RemoveAll
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick