Excel 2003 - Comparing rows of data then hiding duplicates via macro?

[LolaM]

Hey folks, am hoping the collective wisdom on here can point me in the right direction...

I have a large chunk of data, up to 1000 rows by 20 columns, which sometimes contains duplicates (generally each instance of a duplicate having only 2 rows, but potentially more). In the case of a duplicate, only ONE column really needs to be considered - a unique reference number.

I'd like to have a macro review all of the rows and where the data is identical in a given column, hide that row or rows.

So in the example below, I'd want row 3 to be hidden, but rows 1 and 2 to remain shown as they are for different unique reference numbers, despite being for the same name.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]column A[/TD]
[TD] column B[/TD]
[TD]column C, etc[/TD]
[/TR]
[TR]
[TD]row 1[/TD]
[TD]12345[/TD]
[TD]Mr Smith[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]row 2[/TD]
[TD]34567[/TD]
[TD]Mr Smith[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]row 3[/TD]
[TD]12345[/TD]
[TD]Mr Smith[/TD]
[TD]Address[/TD]
[/TR]
</tbody>[/TABLE]

Also, out of curiosity, how would I do the same thing, but hide the row if ALL the data in the row was identical to another row?

I am thinking arrays but I've never used them... does anyone have any simple examples I can use and build on from there?

Thanks in advance!

 

[MickG]

Try this for a start:-
This relates to duplicates in column "A" only.

[COLOR="Navy"]Sub[/COLOR] MG03Jan28
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & 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
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [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.Hidden = True
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Trevor G]

Highlight your column with references in that are duplicated then use the Data Menu and Filter and Advanced Filter then make sure you select the Unique records and this will then filter the duplicate records

 

[hiker95]

LolaM,


Sample raw data:

Excel Workbook
	A	B	C
1	12345	Mr Smith	Address
2	34567	Mr Smith	Address
3	12345	Mr Smith	Address
4	34567	Mr Smith	Address
5	12345	Mr Smith	Address
6	34567	Mr Smith	Address
7
Sheet1

After the macro:

Excel Workbook
	A	B	C
1	12345	Mr Smith	Address
2	34567	Mr Smith	Address
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub HideDupes()
' hiker95, 01/03/2013
' http://www.mrexcel.com/forum/excel-questions/677267-excel-2003-comparing-rows-data-then-hiding-duplicates-via-macro.html
Dim r As Long, lr As Long, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
With Range(Cells(1, lc + 1), Cells(lr, lc + 1))
  .Formula = "=IF(COUNTIF($A$1:A1,A1)=1,1,""#N/A"")"
  .Value = .Value
  .SpecialCells(xlConstants, xlErrors).Rows.Hidden = True
  .ClearContents
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the HideDupes macro.

 

[LolaM]

Try this for a start:-
This relates to duplicates in column "A" only.

[COLOR=Navy]Sub[/COLOR] MG03Jan28
[COLOR=Navy]Dim[/COLOR] Rng     [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn      [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] nRng    [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & 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
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]Set[/COLOR] nRng = Dn
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR=Navy]End[/COLOR] If
    [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.Hidden = True
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Hi Mick, that worked a treat, thanks!!

Couple of stupid questions though...
1. How would I go about changing it so that it limits it to a particular range of cells, for example, A1:A100 only?
2. How would I go about changing it so it compares on TWO columns, and if both columns match on multiple rows, hides the duplicate row(s)?

Thanks again in advance!!

 

[MickG]

This is for Range "A1:A100" and hides rows based on Duplicates in columns "A" & B".
If you wanted columns, say "C & F" then the code line would be:-
Mult = Dn.offset(,2) & Dn.offset(,5)

[COLOR="Navy"]Sub[/COLOR] MG04Jan42
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Mult [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Set Rng = Range("A1:A100") '[COLOR="Green"][B] Changed Range[/B][/COLOR]
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Mult = Dn & Dn.Offset(, 1) '[COLOR="Green"][B]This is columns "A & B" for each row[/B][/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Exists(Mult) [COLOR="Navy"]Then[/COLOR]
        .Add Mult, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [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.Hidden = True
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

MickG,

Always soooooo fast - another for the archives. Thanks again.


If we start with this:

Excel Workbook
	A	B	C
1	12345	Mr Smith	Address
2	34567	Mr Smith	Address
3	12345	Mr Smith	Address
4	34567	Mr Smith	Address
5	12345	Mr Smith	Address
6	34567	Mr Smith	Address
7
8
9
10
Sheet1

We end up with this?

Excel Workbook
	A	B	C
1	12345	Mr Smith	Address
2	34567	Mr Smith	Address
7
101
102
103
104
Sheet1

 

[hiker95]

MickG,

OK, now I understand.

 

[LolaM]

MickG, thanks again - that worked perfectly!!

 

[MickG]

LolaM , Your welcome

hiker95, Thanks for the Interest

Regrds Mick