Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-02
7
Medium Priority
?
358 Views
Last Modified: 2012-05-11
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
Comment
Question by:Parth48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35024509
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
 

Author Comment

by:Parth48
ID: 35024512
please refer the attached files ...


Test.xlsx
03-03-2011-11-39-45-AM.jpg
0
 

Author Comment

by:Parth48
ID: 35024516
in attached snapshot i want to remove duplicate values from marked area (using red line) ...
using macros (including for loop)
0
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

 
LVL 50

Expert Comment

by:Dave Brett
ID: 35024526
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
 

Author Comment

by:Parth48
ID: 35024852
matching an entire column (A,B,C,D,E and F) (not rows)
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 35024932

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
 

Author Comment

by:Parth48
ID: 35026254
hi @brettdj: Thanks very much ...
0

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Progress

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question