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

LolaM · Jan 3, 2013

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.

	column A	column B	column C, etc
row 1	12345	Mr Smith	Address
row 2	34567	Mr Smith	Address
row 3	12345	Mr Smith	Address

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 · Jan 3, 2013

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

Code:

[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 · Jan 3, 2013

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 · Jan 3, 2013

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.

Code:

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 · Jan 4, 2013

MickG said:

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

Code:

[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 · Jan 4, 2013

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)

Code:

[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 · Jan 4, 2013

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 · Jan 4, 2013

MickG,

OK, now I understand.

LolaM · Jan 4, 2013

MickG, thanks again - that worked perfectly!!

MickG · Jan 4, 2013

LolaM , Your welcome

hiker95, Thanks for the Interest

Regrds Mick

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

LolaM

New Member

Excel Facts

MickG

MrExcel MVP

Trevor G

Well-known Member

hiker95

Well-known Member

LolaM

New Member

MickG

MrExcel MVP

hiker95

Well-known Member

hiker95

Well-known Member

LolaM

New Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

Well-known Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock