Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

how can i remove duplicate values from the excel sheet ??

i want to know that how can i remove duplicate values from the excel sheet using excel macros??

here i attach the excel sheet as well as attached snapshot ...

please refer the attached document ....
0
Parth48
Asked:
Parth48
  • 4
  • 3
1 Solution
 
DaveCommented:
Nothing attached :)


But my The Duplicate Master" at this EE article link, http://www.experts-exchange.com/A_2123.html

will handle your requests with functionality for the entireworbook, one, several or all columns in a row etc

Cheers

Dave
0
 
Parth48Author Commented:
please refer the attached files ...


Test.xlsx
03-03-2011-11-39-45-AM.jpg
0
 
Parth48Author Commented:
in attached snapshot i want to remove duplicate values from marked area (using red line) ...
using macros (including for loop)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DaveCommented:
what do you define as a dupe?

- any individual cell that is replicated
- matching an entire row
- matching an entire column (D and F)

My addin will handle the first two, not the last

And do you specifically want code that you can see (the addin is code), and if so why - for you to modify?

Cheers

Dave
0
 
Parth48Author Commented:
matching an entire column (A,B,C,D,E and F) (not rows)
0
 
DaveCommented:

Sample attached

The code runs on a user prompted range, the default being the current selection

While the code could be a litlte shorter I have used arrays for a speed gain over range looops

This line is to show thecolumn(s) contents removed
you can take it out
.Interior.Color = vbYellow

Cheers

Dave
Sub DupSel()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim X
    Dim lcol As Long
    Dim lrow As Long
    Dim strTmp
    Dim objDic
    Set objDic = CreateObject("scripting.dictionary")
    Set rng1 = Application.InputBox("Please select range", , Selection.Address, , , , , 8)
    X = rng1
    For lcol = 1 To UBound(X, 2)
        strTmp = vbNullString
        For lrow = 1 To UBound(X, 1)
            strTmp = strTmp & X(lrow, lcol)
        Next lrow
        If Not objDic.exists(strTmp) Then
            objDic.Add strTmp, 1
        Else
            If Not rng2 Is Nothing Then
                Set rng2 = Union(rng2, rng1.Cells(1).Offset(0, lcol - 1).Resize(lrow - 1, 1))
            Else
                Set rng2 = rng1.Cells(1).Offset(0, lcol - 1).Resize(lrow - 1, 1)
            End If
        End If
    Next lcol
    If Not rng2 Is Nothing Then
        With rng2
            .ClearContents
            .Interior.Color = vbYellow
        End With
    End If
End Sub

Open in new window

test.xlsm
0
 
Parth48Author Commented:
hi @brettdj: Thanks very much ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now